<?php
declare(strict_types=1);
require __DIR__.'/app/bootstrap.php';require_permission('reports.financial.view');require __DIR__.'/app/layout.php';
$pdo=db();$companyId=company_id();$userId=(int)user()['id'];$asOf=(string)($_GET['as_of']??date('Y-m-d'));
if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{$action=(string)($_POST['action']??'');
 if($action==='forecast_generate'){$date=(string)($_POST['forecast_date']??date('Y-m-d'));$days=max(1,min(365,(int)($_POST['horizon_days']??90)));$scenario=(string)($_POST['scenario']??'base');if(!in_array($scenario,['base','conservative','optimistic'],true))throw new RuntimeException('السيناريو غير صالح.');$end=date('Y-m-d',strtotime($date.' +'.$days.' days'));$bankOpening=(float)scalar('SELECT COALESCE(SUM(opening_balance),0) FROM bank_accounts WHERE company_id=? AND is_active=1',[$companyId]);$cashMovement=(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 je.company_id=? AND je.status='posted' AND je.entry_date<=? AND jl.account_id IN (SELECT account_id FROM bank_accounts WHERE company_id=? AND is_active=1 UNION SELECT account_id FROM cashboxes WHERE company_id=? AND is_active=1)",[$companyId,$date,$companyId,$companyId]);$opening=round($bankOpening+$cashMovement,2);$pdo->beginTransaction();$pdo->prepare("UPDATE cash_flow_forecasts SET status='superseded' WHERE company_id=? AND status='approved'")->execute([$companyId]);$pdo->prepare("INSERT INTO cash_flow_forecasts(company_id,forecast_date,horizon_days,scenario,status,opening_cash,prepared_by,created_at) VALUES (?,?,?,?,'draft',?,?,NOW())")->execute([$companyId,$date,$days,$scenario,$opening,$userId]);$id=(int)$pdo->lastInsertId();$insert=$pdo->prepare('INSERT INTO cash_flow_forecast_lines(cash_flow_forecast_id,flow_date,flow_type,source_type,source_id,project_id,description,amount,probability_pct,is_manual) VALUES (?,?,?,?,?,?,?,?,?,0)');$inProb=$scenario==='conservative'?70:100;$outProb=$scenario==='optimistic'?90:100;$sales=$pdo->prepare("SELECT si.id,COALESCE(si.due_date,si.invoice_date) flow_date,si.project_id,si.invoice_no,si.net_due_amount-si.collected_amount amount,bp.legal_name_ar partner FROM sales_invoices si JOIN customers c ON c.id=si.customer_id JOIN business_partners bp ON bp.id=c.partner_id WHERE si.company_id=? AND si.status IN ('issued','partially_collected','overdue') AND si.collected_amount<si.net_due_amount AND COALESCE(si.due_date,si.invoice_date)<=?");$sales->execute([$companyId,$end]);foreach($sales->fetchAll() as $r)$insert->execute([$id,max($date,$r['flow_date']),'inflow','sales_invoice',$r['id'],$r['project_id'],'تحصيل '.$r['invoice_no'].' — '.$r['partner'],$r['amount'],$inProb]);$purchases=$pdo->prepare("SELECT si.id,COALESCE(si.due_date,si.invoice_date) flow_date,si.project_id,si.internal_no,si.total_amount-si.paid_amount amount,bp.legal_name_ar partner FROM supplier_invoices si JOIN suppliers s ON s.id=si.supplier_id JOIN business_partners bp ON bp.id=s.partner_id WHERE si.company_id=? AND si.status IN ('posted','partially_paid') AND si.paid_amount<si.total_amount AND COALESCE(si.due_date,si.invoice_date)<=?");$purchases->execute([$companyId,$end]);foreach($purchases->fetchAll() as $r)$insert->execute([$id,max($date,$r['flow_date']),'outflow','supplier_invoice',$r['id'],$r['project_id'],'سداد '.$r['internal_no'].' — '.$r['partner'],$r['amount'],$outProb]);$pdo->commit();audit('cash_forecast.generate','cash_flow_forecast',(string)$id,['horizon'=>$days,'scenario'=>$scenario]);flash('success','تم إنشاء توقع التدفق النقدي حتى '.$end.'.');
 }elseif($action==='forecast_manual'){$forecastId=(int)($_POST['forecast_id']??0);if(!(int)scalar("SELECT COUNT(*) FROM cash_flow_forecasts WHERE id=? AND company_id=? AND status='draft'",[$forecastId,$companyId]))throw new RuntimeException('التوقع غير متاح للتعديل.');$type=(string)($_POST['flow_type']??'inflow');$amount=round((float)($_POST['amount']??0),2);if(!in_array($type,['inflow','outflow'],true)||$amount<=0)throw new RuntimeException('نوع التدفق أو المبلغ غير صحيح.');$pdo->prepare('INSERT INTO cash_flow_forecast_lines(cash_flow_forecast_id,flow_date,flow_type,description,amount,probability_pct,is_manual) VALUES (?,?,?,?,?,?,1)')->execute([$forecastId,$_POST['flow_date'],$type,trim((string)$_POST['description']),$amount,max(0,min(100,(float)($_POST['probability_pct']??100)))]);audit('cash_forecast.manual_line','cash_flow_forecast',(string)$forecastId);flash('success','تمت إضافة التدفق اليدوي.');
 }elseif($action==='forecast_approve'){$id=(int)($_POST['id']??0);$s=$pdo->prepare("UPDATE cash_flow_forecasts SET status='approved',approved_by=? WHERE id=? AND company_id=? AND status='draft'");$s->execute([$userId,$id,$companyId]);if(!$s->rowCount())throw new RuntimeException('تعذر اعتماد التوقع.');audit('cash_forecast.approve','cash_flow_forecast',(string)$id);flash('success','تم اعتماد توقع التدفق النقدي.');
 }}catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();flash('error',$e instanceof PDOException?'تعذر إنشاء التوقع أو يوجد تشغيل مكرر.':$e->getMessage());}redirect('analytics.php');}
$ar=$pdo->prepare("SELECT bp.legal_name_ar partner,si.invoice_no doc_no,si.invoice_date,si.due_date,si.net_due_amount-si.collected_amount outstanding,GREATEST(DATEDIFF(?,COALESCE(si.due_date,si.invoice_date)),0) age_days,p.name_ar project_name FROM sales_invoices si JOIN customers c ON c.id=si.customer_id JOIN business_partners bp ON bp.id=c.partner_id LEFT JOIN projects p ON p.id=si.project_id WHERE si.company_id=? AND si.status IN ('issued','partially_collected','overdue') AND si.collected_amount<si.net_due_amount ORDER BY age_days DESC");$ar->execute([$asOf,$companyId]);$ar=$ar->fetchAll();$ap=$pdo->prepare("SELECT bp.legal_name_ar partner,si.internal_no doc_no,si.invoice_date,si.due_date,si.total_amount-si.paid_amount outstanding,GREATEST(DATEDIFF(?,COALESCE(si.due_date,si.invoice_date)),0) age_days,p.name_ar project_name FROM supplier_invoices si JOIN suppliers s ON s.id=si.supplier_id JOIN business_partners bp ON bp.id=s.partner_id LEFT JOIN projects p ON p.id=si.project_id WHERE si.company_id=? AND si.status IN ('posted','partially_paid') AND si.paid_amount<si.total_amount ORDER BY age_days DESC");$ap->execute([$asOf,$companyId]);$ap=$ap->fetchAll();function aging_totals(array $rows):array{$t=[0,0,0,0,0];foreach($rows as $r){$d=(int)$r['age_days'];$i=$d<=30?0:($d<=60?1:($d<=90?2:($d<=120?3:4)));$t[$i]+=(float)$r['outstanding'];}return $t;}$arTotals=aging_totals($ar);$apTotals=aging_totals($ap);$forecasts=$pdo->prepare("SELECT f.*,COALESCE(SUM(CASE WHEN l.flow_type='inflow' THEN l.amount*l.probability_pct/100 ELSE 0 END),0) inflows,COALESCE(SUM(CASE WHEN l.flow_type='outflow' THEN l.amount*l.probability_pct/100 ELSE 0 END),0) outflows FROM cash_flow_forecasts f LEFT JOIN cash_flow_forecast_lines l ON l.cash_flow_forecast_id=f.id WHERE f.company_id=? GROUP BY f.id ORDER BY f.id DESC");$forecasts->execute([$companyId]);$forecasts=$forecasts->fetchAll();$selectedForecast=(int)($_GET['forecast_id']??($forecasts[0]['id']??0));$forecastLines=[];if($selectedForecast){$s=$pdo->prepare('SELECT l.* FROM cash_flow_forecast_lines l JOIN cash_flow_forecasts f ON f.id=l.cash_flow_forecast_id WHERE l.cash_flow_forecast_id=? AND f.company_id=? ORDER BY l.flow_date,l.id');$s->execute([$selectedForecast,$companyId]);$forecastLines=$s->fetchAll();}
render_header('analytics','التحليلات المالية','أعمار الديون والتدفقات النقدية المتوقعة.');?>
<form class="panel form-card" method="get"><label>تقرير الأعمار حتى<input type="date" name="as_of" value="<?= e($asOf) ?>"></label><button class="primary-btn">تحديث</button></form>
<?php foreach([['أعمار ديون العملاء',$ar,$arTotals],['أعمار ديون الموردين',$ap,$apTotals]] as [$title,$rows,$totals]):?><section class="panel data-card"><header><h2><?= e($title) ?></h2></header><div class="kpi-grid"><?php foreach(['0–30 يوم','31–60 يوم','61–90 يوم','91–120 يوم','أكثر من 120'] as $i=>$label):?><article class="kpi-card"><div><small><?= e($label) ?></small><strong><?= number_format($totals[$i],2) ?></strong></div></article><?php endforeach;?></div><div class="responsive-table"><table><thead><tr><th>الطرف</th><th>المستند</th><th>المشروع</th><th>الاستحقاق</th><th>العمر</th><th>الرصيد</th></tr></thead><tbody><?php foreach($rows as $r):?><tr><td><?= e($r['partner']) ?></td><td><?= e($r['doc_no']) ?></td><td><?= e($r['project_name']?:'عام') ?></td><td><?= e($r['due_date']?:$r['invoice_date']) ?></td><td><?= (int)$r['age_days'] ?> يوم</td><td><?= number_format((float)$r['outstanding'],2) ?></td></tr><?php endforeach;?></tbody></table></div></section><?php endforeach;?>
<details class="panel quick-add"><summary>＋ إنشاء توقع نقدي</summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="forecast_generate"><div class="form-grid"><label>تاريخ التوقع<input type="date" name="forecast_date" value="<?= date('Y-m-d') ?>"></label><label>الأفق بالأيام<input type="number" min="1" max="365" name="horizon_days" value="90"></label><label>السيناريو<select name="scenario"><option value="base">أساسي</option><option value="conservative">متحفظ</option><option value="optimistic">متفائل</option></select></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><th></th></tr></thead><tbody><?php foreach($forecasts as $f):$ending=(float)$f['opening_cash']+(float)$f['inflows']-(float)$f['outflows'];?><tr><td><?= e($f['forecast_date']) ?></td><td><?= e($f['scenario']) ?></td><td><?= number_format((float)$f['opening_cash'],2) ?></td><td><?= number_format((float)$f['inflows'],2) ?></td><td><?= number_format((float)$f['outflows'],2) ?></td><td><?= number_format($ending,2) ?></td><td><span class="chip"><?= e($f['status']) ?></span></td><td><a href="?forecast_id=<?= (int)$f['id'] ?>#forecast-lines">التفاصيل</a><?php if($f['status']==='draft'):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="forecast_approve"><input type="hidden" name="id" value="<?= (int)$f['id'] ?>"><button>اعتماد</button></form><?php endif;?></td></tr><?php endforeach;?></tbody></table></div></section>
<?php if($selectedForecast):?><details class="panel quick-add"><summary>＋ تدفق يدوي</summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="forecast_manual"><input type="hidden" name="forecast_id" value="<?= $selectedForecast ?>"><div class="form-grid"><label>التاريخ<input type="date" name="flow_date" value="<?= date('Y-m-d') ?>"></label><label>النوع<select name="flow_type"><option value="inflow">داخل</option><option value="outflow">خارج</option></select></label><label>البيان<input name="description" required></label><label>المبلغ<input type="number" step="0.01" min="0.01" name="amount" required></label><label>احتمال التحقق %<input type="number" min="0" max="100" step="0.01" name="probability_pct" value="100"></label></div><button class="primary-btn">إضافة</button></form></details><section class="panel data-card" id="forecast-lines"><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($forecastLines as $l):?><tr><td><?= e($l['flow_date']) ?></td><td><?= $l['flow_type']==='inflow'?'داخل':'خارج' ?></td><td><?= e($l['description']) ?></td><td><?= number_format((float)$l['amount'],2) ?></td><td><?= number_format((float)$l['probability_pct'],1) ?>%</td><td><?= number_format((float)$l['amount']*(float)$l['probability_pct']/100,2) ?></td></tr><?php endforeach;?></tbody></table></div></section><?php endif;?>
<?php render_footer();
