<?php
declare(strict_types=1);
require __DIR__.'/app/bootstrap.php';require_permission('certificates.customer.manage');require __DIR__.'/app/layout.php';
$pdo=db();$companyId=company_id();$userId=(int)user()['id'];$currencyId=(int)scalar('SELECT base_currency_id FROM companies WHERE id=?',[$companyId]);
if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{$action=(string)($_POST['action']??'');
 if($action==='contract_create'){$projectId=(int)$_POST['project_id'];$no=trim((string)$_POST['contract_no']);$title=trim((string)$_POST['contract_title']);if(!$projectId||!$no||!$title)throw new RuntimeException('المشروع ورقم العقد وعنوانه مطلوبة.');$s=$pdo->prepare("INSERT INTO project_contracts(project_id,contract_no,contract_title,contract_type,currency_id,original_value,tax_pct,advance_pct,advance_recovery_pct,retention_pct,starts_on,ends_on,status,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?, 'active',?,NOW(),NOW())");$s->execute([$projectId,$no,$title,(string)($_POST['contract_type']??'lump_sum'),$currencyId,(float)$_POST['original_value'],(float)$_POST['tax_pct'],(float)$_POST['advance_pct'],(float)$_POST['advance_recovery_pct'],(float)$_POST['retention_pct'],($_POST['starts_on']??'')?:null,($_POST['ends_on']??'')?:null,$userId]);audit('contract.create','project_contract',(string)$pdo->lastInsertId());flash('success','تم إنشاء عقد العميل.');
 }elseif($action==='customer_cert_create'){$contractId=(int)$_POST['contract_id'];$contract=$pdo->prepare('SELECT pc.*,p.id project_id FROM project_contracts pc JOIN projects p ON p.id=pc.project_id WHERE pc.id=? AND p.company_id=?');$contract->execute([$contractId,$companyId]);$contract=$contract->fetch();if(!$contract)throw new RuntimeException('العقد غير صالح.');$current=(float)$_POST['current_work_amount'];$materials=(float)$_POST['materials_amount'];$variation=(float)$_POST['variation_amount'];$gross=$current+$materials+$variation;$advance=(float)$_POST['advance_recovery'];$retention=round($gross*(float)$_POST['retention_pct']/100,2);$penalty=(float)$_POST['penalty_amount'];$taxable=max(0,$gross-$advance-$retention-$penalty);$tax=round($taxable*(float)$_POST['tax_pct']/100,2);$net=$taxable+$tax;$no=trim((string)$_POST['certificate_no']);if(!$no||$gross<=0)throw new RuntimeException('رقم المستخلص وقيمة أعمال موجبة مطلوبان.');$s=$pdo->prepare("INSERT INTO customer_certificates(contract_id,project_id,certificate_no,certificate_type,period_from,period_to,internal_date,current_work_amount,materials_on_site_amount,variation_amount,claimed_gross_amount,certified_gross_amount,advance_recovery_amount,retention_amount,penalty_amount,taxable_amount,tax_amount,net_due_amount,status,prepared_by,created_at,updated_at) VALUES (?,?,?,'interim',?,?,?,?,?,?,?,?,?,?,?,?,?,?,'draft',?,NOW(),NOW())");$s->execute([$contractId,$contract['project_id'],$no,($_POST['period_from']??'')?:null,$_POST['period_to'],$_POST['internal_date'],$current,$materials,$variation,$gross,$gross,$advance,$retention,$penalty,$taxable,$tax,$net,$userId]);audit('customer_certificate.create','customer_certificate',(string)$pdo->lastInsertId());flash('success','تم إنشاء مستخلص العميل وصافي الاستحقاق '.number_format($net,2));
 }elseif($action==='subcontract_create'){$projectId=(int)$_POST['project_id'];$partnerId=(int)$_POST['partner_id'];$subId=(int)scalar('SELECT id FROM subcontractors WHERE partner_id=?',[$partnerId]);if(!$subId){$pdo->prepare("INSERT INTO subcontractors(partner_id,qualification_status,is_active) VALUES (?,'qualified',1)")->execute([$partnerId]);$subId=(int)$pdo->lastInsertId();}$s=$pdo->prepare("INSERT INTO subcontracts(company_id,project_id,subcontractor_id,subcontract_no,title,subcontract_type,currency_id,original_value,tax_pct,advance_pct,advance_recovery_pct,retention_pct,starts_on,ends_on,status,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?, 'active',?,NOW(),NOW())");$s->execute([$companyId,$projectId,$subId,trim((string)$_POST['subcontract_no']),trim((string)$_POST['title']),(string)($_POST['subcontract_type']??'lump_sum'),$currencyId,(float)$_POST['original_value'],(float)$_POST['tax_pct'],(float)$_POST['advance_pct'],(float)$_POST['advance_recovery_pct'],(float)$_POST['retention_pct'],($_POST['starts_on']??'')?:null,($_POST['ends_on']??'')?:null,$userId]);audit('subcontract.create','subcontract',(string)$pdo->lastInsertId());flash('success','تم إنشاء عقد مقاول الباطن.');
 }elseif($action==='subcert_create'){$subcontractId=(int)$_POST['subcontract_id'];$sub=$pdo->prepare('SELECT * FROM subcontracts WHERE id=? AND company_id=?');$sub->execute([$subcontractId,$companyId]);$sub=$sub->fetch();if(!$sub)throw new RuntimeException('عقد مقاول الباطن غير صالح.');$current=(float)$_POST['current_work_amount'];$materials=(float)$_POST['materials_amount'];$variation=(float)$_POST['variation_amount'];$gross=$current+$materials+$variation;$advance=(float)$_POST['advance_recovery'];$retention=round($gross*(float)$_POST['retention_pct']/100,2);$penalty=(float)$_POST['penalty_amount'];$taxable=max(0,$gross-$advance-$retention-$penalty);$tax=round($taxable*(float)$_POST['tax_pct']/100,2);$net=$taxable+$tax;$s=$pdo->prepare("INSERT INTO subcontract_certificates(subcontract_id,project_id,certificate_no,period_from,period_to,certificate_date,current_work_amount,materials_on_site_amount,variation_amount,gross_amount,advance_recovery_amount,retention_amount,penalty_amount,taxable_amount,tax_amount,net_payable_amount,status,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'draft',?,NOW(),NOW())");$s->execute([$subcontractId,$sub['project_id'],trim((string)$_POST['certificate_no']),($_POST['period_from']??'')?:null,$_POST['period_to'],$_POST['certificate_date'],$current,$materials,$variation,$gross,$advance,$retention,$penalty,$taxable,$tax,$net,$userId]);audit('subcontract_certificate.create','subcontract_certificate',(string)$pdo->lastInsertId());flash('success','تم إنشاء مستخلص مقاول الباطن وصافي الاستحقاق '.number_format($net,2));
 }elseif(in_array($action,['customer_submit','customer_certify','sub_submit','sub_approve'],true)){$id=(int)$_POST['id'];if($action==='customer_submit'){$s=$pdo->prepare("UPDATE customer_certificates cc JOIN projects p ON p.id=cc.project_id SET cc.status='submitted',cc.submitted_on=CURDATE() WHERE cc.id=? AND p.company_id=? AND cc.status='draft'");$s->execute([$id,$companyId]);}elseif($action==='customer_certify'){$s=$pdo->prepare("UPDATE customer_certificates cc JOIN projects p ON p.id=cc.project_id SET cc.status='certified',cc.certified_on=CURDATE(),cc.reviewed_by=? WHERE cc.id=? AND p.company_id=? AND cc.status='submitted'");$s->execute([$userId,$id,$companyId]);}elseif($action==='sub_submit'){$s=$pdo->prepare("UPDATE subcontract_certificates sc JOIN subcontracts sct ON sct.id=sc.subcontract_id SET sc.status='submitted',sc.submitted_by=? WHERE sc.id=? AND sct.company_id=? AND sc.status='draft'");$s->execute([$userId,$id,$companyId]);}else{$s=$pdo->prepare("UPDATE subcontract_certificates sc JOIN subcontracts sct ON sct.id=sc.subcontract_id SET sc.status='approved',sc.finance_approved_by=?,sc.approved_at=NOW() WHERE sc.id=? AND sct.company_id=? AND sc.status='submitted'");$s->execute([$userId,$id,$companyId]);}if(!$s->rowCount())throw new RuntimeException('تعذر تغيير حالة المستخلص.');flash('success','تم تحديث حالة المستخلص.');}
 }catch(Throwable $e){flash('error',$e instanceof PDOException?'البيانات مكررة أو غير متوافقة مع السجلات الحالية.':$e->getMessage());}redirect('certificates.php');}
$projects=$pdo->prepare("SELECT id,project_no,name_ar FROM projects WHERE company_id=? AND status='active' AND deleted_at IS NULL ORDER BY project_no");$projects->execute([$companyId]);$projects=$projects->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 AND partner_code NOT LIKE 'CUS-%' ORDER BY legal_name_ar");$partners->execute([$companyId]);$partners=$partners->fetchAll();
$contracts=$pdo->prepare("SELECT pc.*,p.project_no,p.name_ar project_name FROM project_contracts pc JOIN projects p ON p.id=pc.project_id WHERE p.company_id=? AND pc.status='active' ORDER BY pc.id DESC");$contracts->execute([$companyId]);$contracts=$contracts->fetchAll();$subcontracts=$pdo->prepare("SELECT sc.*,p.project_no,bp.legal_name_ar subcontractor_name FROM subcontracts sc JOIN projects p ON p.id=sc.project_id JOIN subcontractors sub ON sub.id=sc.subcontractor_id JOIN business_partners bp ON bp.id=sub.partner_id WHERE sc.company_id=? AND sc.status='active' ORDER BY sc.id DESC");$subcontracts->execute([$companyId]);$subcontracts=$subcontracts->fetchAll();
$customerCerts=$pdo->prepare("SELECT cc.*,p.name_ar project_name FROM customer_certificates cc JOIN projects p ON p.id=cc.project_id WHERE p.company_id=? ORDER BY cc.id DESC LIMIT 100");$customerCerts->execute([$companyId]);$customerCerts=$customerCerts->fetchAll();$subCerts=$pdo->prepare("SELECT sc.*,p.name_ar project_name FROM subcontract_certificates sc JOIN projects p ON p.id=sc.project_id JOIN subcontracts sct ON sct.id=sc.subcontract_id WHERE sct.company_id=? ORDER BY sc.id DESC LIMIT 100");$subCerts->execute([$companyId]);$subCerts=$subCerts->fetchAll();
render_header('certificates','المستخلصات والعقود','عقود العملاء ومقاولي الباطن ومستخلصاتهم المالية.');?>
<div class="settings-grid"><details class="panel quick-add"><summary>＋ عقد عميل</summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="contract_create"><label>المشروع<select name="project_id"><?php foreach($projects as $p):?><option value="<?= (int)$p['id'] ?>"><?= e($p['project_no'].' — '.$p['name_ar']) ?></option><?php endforeach;?></select></label><label>رقم العقد<input name="contract_no" required></label><label>العنوان<input name="contract_title" required></label><label>القيمة<input type="number" step="0.01" name="original_value" required></label><label>الضريبة %<input type="number" step="0.01" name="tax_pct" value="15"></label><label>المقدم %<input type="number" step="0.01" name="advance_pct" value="0"></label><label>استرداد المقدم %<input type="number" step="0.01" name="advance_recovery_pct" value="0"></label><label>الاحتجاز %<input type="number" step="0.01" name="retention_pct" value="10"></label><input type="hidden" name="contract_type" value="lump_sum"><button class="primary-btn">حفظ العقد</button></form></details><details class="panel quick-add"><summary>＋ عقد مقاول باطن</summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="subcontract_create"><label>المشروع<select name="project_id"><?php foreach($projects as $p):?><option value="<?= (int)$p['id'] ?>"><?= e($p['project_no'].' — '.$p['name_ar']) ?></option><?php endforeach;?></select></label><label>المقاول<select name="partner_id"><?php foreach($partners as $p):?><option value="<?= (int)$p['id'] ?>"><?= e($p['legal_name_ar']) ?></option><?php endforeach;?></select></label><label>رقم العقد<input name="subcontract_no" required></label><label>العنوان<input name="title" required></label><label>القيمة<input type="number" step="0.01" name="original_value"></label><label>الضريبة %<input type="number" name="tax_pct" value="15"></label><label>المقدم %<input type="number" name="advance_pct" value="0"></label><label>استرداد المقدم %<input type="number" name="advance_recovery_pct" value="0"></label><label>الاحتجاز %<input type="number" name="retention_pct" value="10"></label><input type="hidden" name="subcontract_type" value="lump_sum"><button class="primary-btn">حفظ العقد</button></form></details></div>
<div class="settings-grid"><?php foreach([['customer_cert_create','مستخلص عميل',$contracts,'contract_id'],['subcert_create','مستخلص مقاول باطن',$subcontracts,'subcontract_id']] as [$act,$title,$source,$field]):?><details class="panel quick-add"><summary>＋ <?= e($title) ?></summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="<?= e($act) ?>"><label>العقد<select name="<?= e($field) ?>"><?php foreach($source as $c):?><option value="<?= (int)$c['id'] ?>"><?= e(($c['contract_no']??$c['subcontract_no']).' — '.($c['project_name']??$c['subcontractor_name'])) ?></option><?php endforeach;?></select></label><label>رقم المستخلص<input name="certificate_no" required></label><label>من<input type="date" name="period_from"></label><label>إلى<input type="date" name="period_to" required></label><label>تاريخ المستخلص<input type="date" name="<?= $act==='customer_cert_create'?'internal_date':'certificate_date' ?>" value="<?= date('Y-m-d') ?>"></label><label>أعمال حالية<input type="number" step="0.01" name="current_work_amount" required></label><label>مواد بالموقع<input type="number" step="0.01" name="materials_amount" value="0"></label><label>أوامر تغيير<input type="number" step="0.01" name="variation_amount" value="0"></label><label>استرداد مقدم<input type="number" step="0.01" name="advance_recovery" value="0"></label><label>احتجاز %<input type="number" step="0.01" name="retention_pct" value="10"></label><label>غرامات<input type="number" step="0.01" name="penalty_amount" value="0"></label><label>ضريبة %<input type="number" step="0.01" name="tax_pct" value="15"></label><button class="primary-btn">حفظ كمسودة</button></form></details><?php endforeach;?></div>
<?php foreach([['مستخلصات العملاء',$customerCerts,'customer'],['مستخلصات مقاولي الباطن',$subCerts,'sub']] as [$title,$rows,$kind]):?><section class="panel data-card"><header><h2><?= e($title) ?></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($rows as $r):?><tr><td><?= e($r['certificate_no']) ?></td><td><?= e($r['project_name']) ?></td><td><?= number_format((float)($r[$kind==='customer'?'claimed_gross_amount':'gross_amount']),2) ?></td><td><?= number_format((float)$r['retention_amount'],2) ?></td><td><?= number_format((float)$r['tax_amount'],2) ?></td><td><?= number_format((float)$r[$kind==='customer'?'net_due_amount':'net_payable_amount'],2) ?></td><td><span class="chip"><?= e($r['status']) ?></span></td><td><?php if($r['status']==='draft'):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="<?= $kind==='customer'?'customer_submit':'sub_submit' ?>"><input type="hidden" name="id" value="<?= (int)$r['id'] ?>"><button>تقديم</button></form><?php elseif($r['status']==='submitted'):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="<?= $kind==='customer'?'customer_certify':'sub_approve' ?>"><input type="hidden" name="id" value="<?= (int)$r['id'] ?>"><button>اعتماد</button></form><?php endif;?></td></tr><?php endforeach;?></tbody></table></div></section><?php endforeach;render_footer();
