<?php
declare(strict_types=1);

function read_csv_import(string $field,array $required,array $allowed,int $limit=5000): array {
    $file=$_FILES[$field]??null;
    if(!$file||$file['error']!==UPLOAD_ERR_OK)throw new RuntimeException('Choose a valid CSV file.');
    if((int)$file['size']>5*1024*1024)throw new RuntimeException('The CSV file must be smaller than 5 MB.');
    $handle=fopen($file['tmp_name'],'rb');if(!$handle)throw new RuntimeException('The CSV file could not be read.');
    $header=fgetcsv($handle);if(!$header){fclose($handle);throw new RuntimeException('The CSV file is empty.');}
    $header=array_map(fn($h)=>strtolower(trim(preg_replace('/^\xEF\xBB\xBF/','',(string)$h))),$header);
    foreach($required as $column)if(!in_array($column,$header,true)){fclose($handle);throw new RuntimeException("The CSV must contain a {$column} column.");}
    $rows=[];$line=1;
    while(($values=fgetcsv($handle))!==false){
        $line++;if($line>$limit+1){fclose($handle);throw new RuntimeException("Imports are limited to {$limit} rows at a time.");}
        $row=array_fill_keys($allowed,'');foreach($header as $i=>$column)if(in_array($column,$allowed,true))$row[$column]=trim((string)($values[$i]??''));
        if(implode('',$row)!==''){$row['_line']=$line;$rows[]=$row;}
    }
    fclose($handle);return $rows;
}

function record_document_event(int $documentId,string $eventType,?string $fromStatus=null,?string $toStatus=null,?string $notes=null): void {
    db()->prepare('INSERT INTO document_events (document_id,event_type,from_status,to_status,notes,created_by) VALUES (?,?,?,?,?,?)')->execute([$documentId,$eventType,$fromStatus,$toStatus,$notes,user()['id']??null]);
}

function handle_actions(): void {
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') return;
    verify_csrf();
    $action = (string)($_POST['action'] ?? '');
    try {
        if(!billing_schema_is_current())throw new RuntimeException('The owner must run the controlled database upgrade before records can be changed.');
        $permissionMap=[
            'save_customer'=>'customers.manage','merge_customer'=>'customers.manage','delete_unused_customer'=>'customers.manage',
            'save_crm_opportunity'=>'crm.manage','update_crm_stage'=>'crm.manage','save_crm_activity'=>'crm.manage','complete_crm_activity'=>'crm.manage',
            'save_cylinder_asset'=>'operations.manage','update_cylinder_asset'=>'operations.manage','start_cylinder_refill'=>'operations.manage','save_refill_job'=>'operations.manage','create_refill_invoice'=>'documents.create','update_refill_job'=>'operations.manage','save_delivery'=>'operations.manage','update_delivery'=>'operations.manage',
            'import_customers'=>'imports.manage','import_products'=>'imports.manage','import_suppliers'=>'imports.manage','import_bills'=>'imports.manage','import_expenses'=>'imports.manage','import_invoices'=>'imports.manage','normalise_imported_invoices'=>'imports.manage',
            'save_product'=>'catalogue.manage','sync_website_products'=>'catalogue.manage','bulk_set_stock_counts'=>'stock.adjust','set_stock_count'=>'stock.adjust','adjust_stock'=>'stock.adjust',
            'update_web_order'=>'website_orders.manage','convert_web_order'=>'website_orders.manage',
            'save_document'=>'documents.create','set_document_status'=>'documents.manage','fulfil_invoice'=>'documents.manage','toggle_invoice_share'=>'documents.manage','convert_quote'=>'documents.create',
            'open_whatsapp'=>'reminders.manage','send_whatsapp_api'=>'whatsapp.manage','update_whatsapp_conversation'=>'whatsapp.manage','link_whatsapp_customer'=>'whatsapp.manage','create_customer_from_whatsapp'=>'whatsapp.manage','start_whatsapp_conversation'=>'whatsapp.manage',
            'record_payment'=>'payments.record','create_invoice_paynow_link'=>'payments.record','refresh_invoice_paynow_status'=>'payments.record','save_expense'=>'expenses.manage','save_supplier'=>'purchasing.manage','save_bill'=>'purchasing.manage','record_bill_payment'=>'purchasing.manage',
            'save_settings'=>'settings.manage','save_payment_account'=>'settings.manage','update_user'=>'users.manage','reset_user_password'=>'users.manage','add_user'=>'users.manage',
        ];
        if($action !== 'change_own_password' && !isset($permissionMap[$action]))throw new RuntimeException('This action is not registered in the access-control policy.');
        if(isset($permissionMap[$action]))require_permission($permissionMap[$action]);
        switch ($action) {
            case 'save_customer':
                $id=(int)($_POST['id']??0); $name=trim($_POST['name']??''); if($name==='') throw new RuntimeException('Customer name is required.');
                $customerType=in_array($_POST['customer_type']??'Household',['Household','Corporate','Restaurant / Cafe','Hotel / Lodge','Retailer','Other'],true)?$_POST['customer_type']:'Household';
                $preferredPayment=in_array($_POST['preferred_payment']??'',['Cash','EcoCash','Bank transfer','POS / Swipe','Paynow','OneMoney','InnBucks',"O'mari",''],true)?$_POST['preferred_payment']:'';
                $values=[$name,trim($_POST['company_name']??''),trim($_POST['email']??''),trim($_POST['phone']??''),trim($_POST['whatsapp']??''),trim($_POST['tax_number']??''),trim($_POST['address']??''),trim($_POST['city']??''),$customerType,trim($_POST['area']??''),$preferredPayment,isset($_POST['marketing_consent'])?1:0,($_POST['next_refill_due']??'')?:null,trim($_POST['notes']??'')];
                if($id){$values[]=$id;db()->prepare('UPDATE customers SET name=?,company_name=?,email=?,phone=?,whatsapp=?,tax_number=?,address=?,city=?,customer_type=?,area=?,preferred_payment=?,marketing_consent=?,next_refill_due=?,notes=? WHERE id=?')->execute($values);}else{db()->prepare('INSERT INTO customers (name,company_name,email,phone,whatsapp,tax_number,address,city,customer_type,area,preferred_payment,marketing_consent,next_refill_due,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute($values);$id=(int)db()->lastInsertId();}
                audit($id?'customer_saved':'customer_created','customer',$id); flash('success','Customer saved.'); redirect('index.php?page=customers');

            case 'merge_customer':
                require_role(['owner','admin']);$masterId=(int)($_POST['master_customer_id']??0);$sourceId=(int)($_POST['source_customer_id']??0);if(!$masterId||!$sourceId||$masterId===$sourceId)throw new RuntimeException('Choose two different customer records.');if(($_POST['confirm_merge']??'')!=='yes')throw new RuntimeException('Confirm that you reviewed the two customer records.');
                $referenceTables=['documents','web_orders','crm_opportunities','crm_activities','refill_jobs','deliveries','whatsapp_conversations','whatsapp_messages','cylinder_assets'];$fkStmt=db()->query("SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA=DATABASE() AND REFERENCED_TABLE_NAME='customers'");$unknown=[];foreach($fkStmt->fetchAll() as $foreignKey){if($foreignKey['COLUMN_NAME']!=='customer_id'||!in_array($foreignKey['TABLE_NAME'],$referenceTables,true))$unknown[]=$foreignKey['TABLE_NAME'].'.'.$foreignKey['COLUMN_NAME'];}if($unknown)throw new RuntimeException('The merge was stopped because a new customer-linked area needs review: '.implode(', ',$unknown));
                db()->beginTransaction();$customersStmt=db()->prepare('SELECT * FROM customers WHERE id IN (?,?) FOR UPDATE');$customersStmt->execute([$masterId,$sourceId]);$customersById=[];foreach($customersStmt->fetchAll() as $customer)$customersById[(int)$customer['id']]=$customer;if(!isset($customersById[$masterId],$customersById[$sourceId]))throw new RuntimeException('One of the customer records could not be found.');$master=$customersById[$masterId];$source=$customersById[$sourceId];if(!(int)$master['active'])throw new RuntimeException('The customer record being kept must be active.');
                $moved=[];foreach($referenceTables as $table){$countStmt=db()->prepare("SELECT COUNT(*) FROM `{$table}` WHERE customer_id=?");$countStmt->execute([$sourceId]);$moved[$table]=(int)$countStmt->fetchColumn();if($moved[$table])db()->prepare("UPDATE `{$table}` SET customer_id=? WHERE customer_id=?")->execute([$masterId,$sourceId]);}$auditMove=db()->prepare('UPDATE audit_logs SET entity_id=? WHERE entity_type="customer" AND entity_id=?');$auditMove->execute([$masterId,$sourceId]);$moved['audit_logs']=$auditMove->rowCount();
                $merged=$master;foreach(['company_name','email','phone','whatsapp','tax_number','address','city','area','preferred_payment'] as $field){if(trim((string)($merged[$field]??''))===''&&trim((string)($source[$field]??''))!=='')$merged[$field]=$source[$field];}$lastDates=array_filter([(string)($master['last_contact_at']??''),(string)($source['last_contact_at']??'')]);$refillDates=array_filter([(string)($master['next_refill_due']??''),(string)($source['next_refill_due']??'')]);$merged['last_contact_at']=$lastDates?max($lastDates):null;$merged['next_refill_due']=$refillDates?max($refillDates):null;$merged['marketing_consent']=isset($_POST['carry_marketing_consent'])?max((int)$master['marketing_consent'],(int)$source['marketing_consent']):(int)$master['marketing_consent'];$sourceNote=trim((string)($source['notes']??''));$merged['notes']=trim((string)($master['notes']??'').($sourceNote!==''?"\n\nMerged from customer #{$sourceId} ({$source['name']}): ".$sourceNote:''));
                db()->prepare('UPDATE customers SET company_name=?,email=?,phone=?,whatsapp=?,tax_number=?,address=?,city=?,area=?,preferred_payment=?,marketing_consent=?,last_contact_at=?,next_refill_due=?,notes=? WHERE id=?')->execute([$merged['company_name'],$merged['email'],$merged['phone'],$merged['whatsapp'],$merged['tax_number'],$merged['address'],$merged['city'],$merged['area'],$merged['preferred_payment'],$merged['marketing_consent'],$merged['last_contact_at'],$merged['next_refill_due'],$merged['notes'],$masterId]);db()->prepare('INSERT INTO customer_merge_log (action_type,master_customer_id,source_customer_id,master_snapshot,source_snapshot,moved_records,performed_by) VALUES ("merge",?,?,?,?,?,?)')->execute([$masterId,$sourceId,json_encode($master,JSON_UNESCAPED_UNICODE),json_encode($source,JSON_UNESCAPED_UNICODE),json_encode($moved),user()['id']]);db()->prepare('DELETE FROM customers WHERE id=?')->execute([$sourceId]);db()->commit();audit('customer_merged','customer',$masterId,'Merged customer #'.$sourceId.'; moved '.json_encode($moved));flash('success',$source['name'].' was merged safely into '.$master['name'].'.');redirect('customer-cleanup.php');

            case 'delete_unused_customer':
                require_role(['owner','admin']);$customerId=(int)($_POST['customer_id']??0);if(!$customerId||($_POST['confirm_delete']??'')!=='yes')throw new RuntimeException('Confirm the unused contact you want to delete.');$referenceTables=['documents','web_orders','crm_opportunities','crm_activities','refill_jobs','deliveries','whatsapp_conversations','whatsapp_messages','cylinder_assets'];db()->beginTransaction();$customerStmt=db()->prepare('SELECT * FROM customers WHERE id=? FOR UPDATE');$customerStmt->execute([$customerId]);$customer=$customerStmt->fetch();if(!$customer)throw new RuntimeException('Customer record not found.');$linked=[];foreach($referenceTables as $table){$countStmt=db()->prepare("SELECT COUNT(*) FROM `{$table}` WHERE customer_id=?");$countStmt->execute([$customerId]);if($count=(int)$countStmt->fetchColumn())$linked[$table]=$count;}if($linked)throw new RuntimeException('This contact has linked business history and cannot be deleted. Merge it instead.');db()->prepare('INSERT INTO customer_merge_log (action_type,source_customer_id,source_snapshot,moved_records,performed_by) VALUES ("delete_unused",?,?,?,?)')->execute([$customerId,json_encode($customer,JSON_UNESCAPED_UNICODE),json_encode([]),user()['id']]);db()->prepare('DELETE FROM customers WHERE id=?')->execute([$customerId]);db()->commit();audit('unused_customer_deleted','customer',$customerId,$customer['name']);flash('success','Unused contact '.$customer['name'].' was deleted.');redirect('customer-cleanup.php');

            case 'save_crm_opportunity':
                $id=(int)($_POST['id']??0);$customerId=(int)($_POST['customer_id']??0);$title=trim($_POST['title']??'');if(!$customerId||$title==='')throw new RuntimeException('Select a customer and enter an opportunity title.');
                $stages=['new_enquiry','contacted','quotation_sent','order_confirmed','awaiting_payment','invoiced','preparing_order','out_for_delivery','completed','lost'];$stage=in_array($_POST['stage']??'',$stages,true)?$_POST['stage']:'new_enquiry';$sources=['WhatsApp','Website','Walk-in','Phone','Facebook','Instagram','Referral','Other'];$source=in_array($_POST['source']??'',$sources,true)?$_POST['source']:'WhatsApp';$value=max(0,decimal($_POST['value']??0));$currency=preg_match('/^[A-Z]{3}$/',strtoupper($_POST['currency_code']??''))?strtoupper($_POST['currency_code']):'USD';$assigned=(int)($_POST['assigned_to']??0)?:null;$expected=($_POST['expected_close_date']??'')?:null;$followUp=($_POST['next_follow_up_at']??'')?date('Y-m-d H:i:s',strtotime($_POST['next_follow_up_at'])):null;$notes=trim($_POST['notes']??'');$lostReason=$stage==='lost'?trim($_POST['lost_reason']??''):null;
                if($id){db()->prepare('UPDATE crm_opportunities SET customer_id=?,title=?,stage=?,source=?,value=?,currency_code=?,expected_close_date=?,next_follow_up_at=?,assigned_to=?,notes=?,lost_reason=? WHERE id=?')->execute([$customerId,$title,$stage,$source,$value,$currency,$expected,$followUp,$assigned,$notes,$lostReason,$id]);}else{db()->prepare('INSERT INTO crm_opportunities (customer_id,title,stage,source,value,currency_code,expected_close_date,next_follow_up_at,assigned_to,notes,lost_reason,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')->execute([$customerId,$title,$stage,$source,$value,$currency,$expected,$followUp,$assigned,$notes,$lostReason,user()['id']]);$id=(int)db()->lastInsertId();}
                audit('crm_opportunity_saved','crm_opportunity',$id,$stage);flash('success','CRM opportunity saved.');redirect('index.php?page=crm&opportunity='.$id);

            case 'update_crm_stage':
                $id=(int)($_POST['id']??0);$stages=['new_enquiry','contacted','quotation_sent','order_confirmed','awaiting_payment','invoiced','preparing_order','out_for_delivery','completed','lost'];$stage=(string)($_POST['stage']??'');if(!$id||!in_array($stage,$stages,true))throw new RuntimeException('Choose a valid CRM stage.');db()->prepare('UPDATE crm_opportunities SET stage=?,lost_reason=? WHERE id=?')->execute([$stage,$stage==='lost'?trim($_POST['lost_reason']??''):null,$id]);audit('crm_stage_changed','crm_opportunity',$id,$stage);flash('success','Opportunity moved to '.str_replace('_',' ',$stage).'.');redirect('index.php?page=crm&opportunity='.$id);

            case 'save_crm_activity':
                $opportunityId=(int)($_POST['opportunity_id']??0)?:null;$customerId=(int)($_POST['customer_id']??0);if($opportunityId){$find=db()->prepare('SELECT customer_id FROM crm_opportunities WHERE id=?');$find->execute([$opportunityId]);$customerId=(int)$find->fetchColumn();}if(!$customerId)throw new RuntimeException('Select a customer or opportunity.');$types=['call','whatsapp','email','meeting','task','note'];$type=in_array($_POST['activity_type']??'',$types,true)?$_POST['activity_type']:'task';$subject=trim($_POST['subject']??'');if($subject==='')throw new RuntimeException('Enter the activity subject.');$due=($_POST['due_at']??'')?date('Y-m-d H:i:s',strtotime($_POST['due_at'])):null;$assigned=(int)($_POST['assigned_to']??0)?:null;db()->prepare('INSERT INTO crm_activities (opportunity_id,customer_id,activity_type,subject,due_at,notes,assigned_to,created_by) VALUES (?,?,?,?,?,?,?,?)')->execute([$opportunityId,$customerId,$type,$subject,$due,trim($_POST['notes']??''),$assigned,user()['id']]);db()->prepare('UPDATE customers SET last_contact_at=IF(?="note",last_contact_at,NOW()) WHERE id=?')->execute([$type,$customerId]);audit('crm_activity_created','crm_activity',(int)db()->lastInsertId(),$type);flash('success','CRM activity added.');redirect('index.php?page=crm'.($opportunityId?'&opportunity='.$opportunityId:''));

            case 'complete_crm_activity':
                $id=(int)($_POST['id']??0);if(!$id)throw new RuntimeException('Activity not found.');db()->prepare('UPDATE crm_activities SET completed_at=COALESCE(completed_at,NOW()) WHERE id=?')->execute([$id]);audit('crm_activity_completed','crm_activity',$id);flash('success','Follow-up completed.');redirect($_SERVER['HTTP_REFERER']??'index.php?page=crm');

            case 'save_cylinder_asset':
                $code=strtoupper(trim($_POST['cylinder_code']??''));if($code==='')throw new RuntimeException('Enter a cylinder identification code.');
                $types=['pink_quick_connect','blue_screw','compatible_other'];$type=in_array($_POST['cylinder_type']??'',$types,true)?$_POST['cylinder_type']:'compatible_other';
                $ownership=($_POST['ownership']??'soda_zw')==='customer'?'customer':'soda_zw';$customerId=(int)($_POST['customer_id']??0)?:null;if($ownership==='customer'&&!$customerId)throw new RuntimeException('Select the owner of this customer cylinder.');
                $statuses=['in_stock_full','in_stock_empty','received','refilling','ready','with_customer','damaged','retired'];$status=in_array($_POST['status']??'',$statuses,true)?$_POST['status']:'in_stock_full';
                db()->beginTransaction();db()->prepare('INSERT INTO cylinder_assets (cylinder_code,customer_id,cylinder_type,ownership,status,location,next_inspection_due,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?)')->execute([$code,$customerId,$type,$ownership,$status,trim($_POST['location']??''),($_POST['next_inspection_due']??'')?:null,trim($_POST['notes']??''),user()['id']]);$id=(int)db()->lastInsertId();db()->prepare('INSERT INTO cylinder_events (cylinder_id,event_type,to_status,notes,created_by) VALUES (? ,"created",?,?,?)')->execute([$id,$status,'Cylinder added to Soda ZW register',user()['id']]);db()->commit();audit('cylinder_created','cylinder',$id,$code);flash('success','Cylinder '.$code.' added to the register.');redirect('index.php?page=operations&cylinder='.$id);

            case 'update_cylinder_asset':
                $id=(int)($_POST['id']??0);if(!$id)throw new RuntimeException('Cylinder record not found.');$statuses=['in_stock_full','in_stock_empty','received','refilling','ready','with_customer','damaged','retired'];$status=in_array($_POST['status']??'',$statuses,true)?$_POST['status']:'';if($status==='')throw new RuntimeException('Choose a valid cylinder status.');
                $find=db()->prepare('SELECT status FROM cylinder_assets WHERE id=? FOR UPDATE');db()->beginTransaction();$find->execute([$id]);$previous=$find->fetchColumn();if($previous===false)throw new RuntimeException('Cylinder record not found.');$customerId=(int)($_POST['customer_id']??0)?:null;db()->prepare('UPDATE cylinder_assets SET status=?,customer_id=?,location=?,next_inspection_due=?,notes=?,last_refill_at=IF(?="in_stock_full" OR ?="ready",COALESCE(last_refill_at,NOW()),last_refill_at) WHERE id=?')->execute([$status,$customerId,trim($_POST['location']??''),($_POST['next_inspection_due']??'')?:null,trim($_POST['notes']??''),$status,$status,$id]);db()->prepare('INSERT INTO cylinder_events (cylinder_id,event_type,from_status,to_status,notes,created_by) VALUES (? ,"status",?,?,?,?)')->execute([$id,$previous,$status,trim($_POST['event_notes']??'Manual cylinder update'),user()['id']]);db()->commit();audit('cylinder_updated','cylinder',$id,$status);flash('success','Cylinder status updated.');redirect('index.php?page=operations&cylinder='.$id);

            case 'start_cylinder_refill':
                $cylinderId=(int)($_POST['cylinder_id']??0);$customerId=(int)($_POST['customer_id']??0);if(!$cylinderId||!$customerId)throw new RuntimeException('Select the cylinder and customer.');$fulfilment=($_POST['fulfilment_method']??'Pickup')==='Delivery'?'Delivery':'Pickup';$deliveryFee=max(0,decimal($_POST['delivery_fee']??0));$address=trim($_POST['delivery_address']??'');if($fulfilment==='Delivery'&&($deliveryFee<=0||$address===''))throw new RuntimeException('Enter the delivery address and agreed delivery fee.');$received=date('Y-m-d H:i:s');$due=date('Y-m-d H:i:s',strtotime('+1 day'));$reference='SZW-RF-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));db()->beginTransaction();$assetStmt=db()->prepare('SELECT cylinder_code,cylinder_type,status FROM cylinder_assets WHERE id=? FOR UPDATE');$assetStmt->execute([$cylinderId]);$asset=$assetStmt->fetch();if(!$asset)throw new RuntimeException('Cylinder not found.');db()->prepare('INSERT INTO refill_jobs (reference,customer_id,cylinder_type,cylinder_identifier,cylinder_id,compatibility_confirmed,service_type,quantity,unit_price,fulfilment_method,delivery_address,delivery_fee,status,payment_status,received_at,due_at,notes,created_by) VALUES (?,?,?,?,?,1,"refill",1,35,?,?,?,"received","unpaid",?,?,?,?)')->execute([$reference,$customerId,$asset['cylinder_type'],$asset['cylinder_code'],$cylinderId,$fulfilment,$address,$deliveryFee,$received,$due,trim($_POST['notes']??''),user()['id']]);$refillId=(int)db()->lastInsertId();db()->prepare('UPDATE cylinder_assets SET status="received",customer_id=?,location="100 Livingstone Avenue" WHERE id=?')->execute([$customerId,$cylinderId]);db()->prepare('INSERT INTO cylinder_events (cylinder_id,refill_job_id,event_type,from_status,to_status,notes,created_by) VALUES (?,? ,"received",?,"received",?,?)')->execute([$cylinderId,$refillId,$asset['status'],'Started refill '.$reference,user()['id']]);db()->prepare('INSERT INTO refill_job_events (refill_job_id,event_type,event_label,notes,created_by) VALUES (? ,"created","Cylinder received",?,?)')->execute([$refillId,'Created from cylinder register',user()['id']]);if($fulfilment==='Delivery'){$deliveryReference='SZW-DL-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));db()->prepare('INSERT INTO deliveries (reference,customer_id,refill_job_id,delivery_address,area,delivery_fee,scheduled_for,status,notes,created_by) SELECT ?,?,?,?,c.area,?,? ,"scheduled",?,? FROM customers c WHERE c.id=?')->execute([$deliveryReference,$customerId,$refillId,$address,$deliveryFee,$due,'Created with refill '.$reference,user()['id'],$customerId]);}db()->commit();audit('cylinder_refill_started','refill_job',$refillId,$reference);flash('success','Overnight refill '.$reference.' started.');redirect('index.php?page=refills&id='.$refillId);

            case 'save_refill_job':
                $customerId=(int)($_POST['customer_id']??0);if(!$customerId)throw new RuntimeException('Select the refill customer.');$cylinderTypes=['pink_quick_connect','blue_screw','compatible_other'];$cylinder=in_array($_POST['cylinder_type']??'',$cylinderTypes,true)?$_POST['cylinder_type']:'compatible_other';$serviceTypes=['new_cylinder','refill','exchange'];$service=in_array($_POST['service_type']??'',$serviceTypes,true)?$_POST['service_type']:'refill';$quantity=max(1,(int)($_POST['quantity']??1));$defaultPrice=$service==='refill'?35:($cylinder==='pink_quick_connect'?55:60);$unitPrice=max(0,decimal($_POST['unit_price']??$defaultPrice));$fulfilment=($_POST['fulfilment_method']??'Pickup')==='Delivery'?'Delivery':'Pickup';$deliveryFee=max(0,decimal($_POST['delivery_fee']??0));if($fulfilment==='Delivery'&&$deliveryFee<=0)throw new RuntimeException('Enter the delivery fee agreed for the customer location.');$received=($_POST['received_at']??'')?date('Y-m-d H:i:s',strtotime($_POST['received_at'])):date('Y-m-d H:i:s');$due=($_POST['due_at']??'')?date('Y-m-d H:i:s',strtotime($_POST['due_at'])):date('Y-m-d H:i:s',strtotime($received.' +1 day'));$opportunity=(int)($_POST['opportunity_id']??0)?:null;
                $cylinderId=(int)($_POST['cylinder_id']??0)?:null;$cylinderIdentifier=trim($_POST['cylinder_identifier']??'');$compatibility=isset($_POST['compatibility_confirmed'])?1:0;if($cylinderId){$assetStmt=db()->prepare('SELECT cylinder_code,cylinder_type FROM cylinder_assets WHERE id=?');$assetStmt->execute([$cylinderId]);$asset=$assetStmt->fetch();if(!$asset)throw new RuntimeException('The selected cylinder is no longer available.');$cylinder=$asset['cylinder_type'];$cylinderIdentifier=$asset['cylinder_code'];$compatibility=1;}if($cylinder==='compatible_other'&&!$compatibility)throw new RuntimeException('Confirm compatibility before accepting this cylinder.');
                $reference='SZW-RF-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));db()->beginTransaction();db()->prepare('INSERT INTO refill_jobs (reference,customer_id,opportunity_id,cylinder_type,cylinder_identifier,cylinder_id,compatibility_confirmed,condition_received,service_type,quantity,unit_price,fulfilment_method,delivery_address,delivery_fee,status,payment_status,received_at,due_at,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,"received",?,?,?,?,?)')->execute([$reference,$customerId,$opportunity,$cylinder,$cylinderIdentifier,$cylinderId,$compatibility,trim($_POST['condition_received']??''),$service,$quantity,$unitPrice,$fulfilment,trim($_POST['delivery_address']??''),$deliveryFee,($_POST['payment_status']??'unpaid')==='paid'?'paid':'unpaid',$received,$due,trim($_POST['notes']??''),user()['id']]);$refillId=(int)db()->lastInsertId();if($cylinderId){db()->prepare('UPDATE cylinder_assets SET status="received",customer_id=?,location="100 Livingstone Avenue" WHERE id=?')->execute([$customerId,$cylinderId]);db()->prepare('INSERT INTO cylinder_events (cylinder_id,refill_job_id,event_type,to_status,notes,created_by) VALUES (?,?,"received","received",?,?)')->execute([$cylinderId,$refillId,'Received for refill '.$reference,user()['id']]);}if($fulfilment==='Delivery'){$deliveryReference='SZW-DL-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));db()->prepare('INSERT INTO deliveries (reference,customer_id,opportunity_id,refill_job_id,delivery_address,area,delivery_fee,scheduled_for,status,notes,created_by) SELECT ?,?,?,?,?,c.area,?,?,"scheduled",?,? FROM customers c WHERE c.id=?')->execute([$deliveryReference,$customerId,$opportunity,$refillId,trim($_POST['delivery_address']??''),$deliveryFee,$due,'Created with refill '.$reference,user()['id'],$customerId]);}db()->commit();audit('refill_job_created','refill_job',$refillId,$reference);flash('success','Refill job '.$reference.' created with overnight due time.');redirect('index.php?page=refills&id='.$refillId);

            case 'create_refill_invoice':
                $refillId=(int)($_POST['refill_id']??0);if(!$refillId)throw new RuntimeException('Refill job not found.');db()->beginTransaction();
                $find=db()->prepare('SELECT r.*,c.area FROM refill_jobs r JOIN customers c ON c.id=r.customer_id WHERE r.id=? FOR UPDATE');$find->execute([$refillId]);$job=$find->fetch();if(!$job)throw new RuntimeException('Refill job not found.');if($job['status']==='cancelled')throw new RuntimeException('A cancelled refill job cannot be invoiced.');
                if(!empty($job['document_id'])){$invoiceId=(int)$job['document_id'];db()->commit();redirect('index.php?page=document-view&id='.$invoiceId);}
                $sku=$job['service_type']==='refill'||$job['service_type']==='exchange'?'SZW-CO2-REFILL':($job['cylinder_type']==='pink_quick_connect'?'SZW-QC-CYL':($job['cylinder_type']==='blue_screw'?'SZW-CL-CYL':''));$product=null;if($sku!==''){$productStmt=db()->prepare('SELECT id,name,cost_price FROM products WHERE sku=? AND active=1 LIMIT 1');$productStmt->execute([$sku]);$product=$productStmt->fetch();}
                $cylinderLabel=['pink_quick_connect'=>'Pink Quick Connect','blue_screw'=>'Blue screw-in','compatible_other'=>'Compatible customer cylinder'][$job['cylinder_type']]??'Compatible cylinder';$serviceLabel=['refill'=>'CO2 cylinder refill','new_cylinder'=>'New CO2 cylinder','exchange'=>'CO2 cylinder exchange'][$job['service_type']]??'CO2 cylinder service';$description=$serviceLabel.' - '.$cylinderLabel;$quantity=max(1,(int)$job['quantity']);$unitPrice=(float)$job['unit_price'];$subtotal=round($quantity*$unitPrice,2);$deliveryFee=$job['fulfilment_method']==='Delivery'?(float)$job['delivery_fee']:0.0;$total=round($subtotal+$deliveryFee,2);
                $number=reserve_unique_number('invoice');$issue=date('Y-m-d');$due=date('Y-m-d',strtotime('+'.max(0,(int)(settings()['default_due_days']??7)).' days'));$shareToken=bin2hex(random_bytes(32));$notes='Refill job '.$job['reference'].'. Overnight service. '.trim((string)$job['notes']);
                $insert=db()->prepare('INSERT INTO documents (type,number,customer_id,issue_date,due_date,status,currency_code,subtotal,discount_type,discount_value,discount_total,tax_total,delivery_fee,total,notes,terms,customer_reference,order_source,fulfilment_method,delivery_address,share_token,created_by) VALUES ("invoice",?,?,?,? ,"draft",?,? ,"fixed",0,0,0,?,?,?, ?,?,"Refill service",?,?,?,?)');
                $insert->execute([$number,$job['customer_id'],$issue,$due,(string)(settings()['currency_code']??'USD'),$subtotal,$deliveryFee,$total,$notes,(string)(settings()['invoice_terms']??''),$job['reference'],$job['fulfilment_method'],$job['delivery_address'],$shareToken,user()['id']]);$invoiceId=(int)db()->lastInsertId();
                db()->prepare('INSERT INTO document_items (document_id,product_id,description,quantity,unit_price,unit_cost,tax_rate,line_subtotal,line_tax,line_total,sort_order) VALUES (?,?,?,?,?,?,0,?,0,?,0)')->execute([$invoiceId,$product['id']??null,$description,$quantity,$unitPrice,(float)($product['cost_price']??0),$subtotal,$subtotal]);
                db()->prepare('UPDATE refill_jobs SET document_id=?,payment_status="unpaid" WHERE id=?')->execute([$invoiceId,$refillId]);db()->prepare('UPDATE deliveries SET document_id=? WHERE refill_job_id=?')->execute([$invoiceId,$refillId]);if(!empty($job['opportunity_id']))db()->prepare('UPDATE crm_opportunities SET document_id=?,stage="invoiced",value=? WHERE id=?')->execute([$invoiceId,$total,$job['opportunity_id']]);db()->prepare('INSERT INTO refill_job_events (refill_job_id,event_type,event_label,notes,created_by) VALUES (?,"invoice","Invoice created",?,?)')->execute([$refillId,'Invoice '.$number.' linked to this job',user()['id']]);
                db()->commit();audit('refill_job_invoiced','refill_job',$refillId,$number);flash('success','Draft invoice '.$number.' created from refill job '.$job['reference'].'.');redirect('index.php?page=document-view&id='.$invoiceId);

            case 'update_refill_job':
                $id=(int)($_POST['id']??0);$statuses=['received','awaiting_refill','ready','out_for_delivery','completed','cancelled'];$status=(string)($_POST['status']??'');if(!$id||!in_array($status,$statuses,true))throw new RuntimeException('Choose a valid refill status.');$payment=in_array($_POST['payment_status']??'',['unpaid','partial','paid'],true)?$_POST['payment_status']:'unpaid';db()->beginTransaction();$jobStmt=db()->prepare('SELECT customer_id,cylinder_id,status,reference FROM refill_jobs WHERE id=? FOR UPDATE');$jobStmt->execute([$id]);$job=$jobStmt->fetch();if(!$job)throw new RuntimeException('Refill job not found.');db()->prepare('UPDATE refill_jobs SET status=?,payment_status=?,completed_at=IF(?="completed",NOW(),completed_at) WHERE id=?')->execute([$status,$payment,$status,$id]);db()->prepare('INSERT INTO refill_job_events (refill_job_id,event_type,event_label,notes,created_by) VALUES (? ,"status",?,?,?)')->execute([$id,ucwords(str_replace('_',' ',$status)),trim($_POST['event_notes']??''),user()['id']]);if(!empty($job['cylinder_id'])){$assetStatus=['received'=>'received','awaiting_refill'=>'refilling','ready'=>'ready','out_for_delivery'=>'with_customer','completed'=>'with_customer'][$status]??null;if($assetStatus){db()->prepare('UPDATE cylinder_assets SET status=?,customer_id=?,location=?,last_refill_at=IF(?="ready",NOW(),last_refill_at) WHERE id=?')->execute([$assetStatus,$job['customer_id'],in_array($assetStatus,['received','refilling','ready'],true)?'100 Livingstone Avenue':'With customer',$assetStatus,$job['cylinder_id']]);db()->prepare('INSERT INTO cylinder_events (cylinder_id,refill_job_id,event_type,from_status,to_status,notes,created_by) VALUES (?,?,"refill",?,?,?,?)')->execute([$job['cylinder_id'],$id,$job['status'],$assetStatus,'Refill '.$job['reference'].' updated',user()['id']]);}}if($status==='completed'){$customerId=(int)$job['customer_id'];db()->prepare('UPDATE customers SET next_refill_due=DATE_ADD(CURDATE(),INTERVAL 60 DAY),last_contact_at=NOW() WHERE id=?')->execute([$customerId]);db()->prepare('UPDATE deliveries SET status="delivered",delivered_at=NOW() WHERE refill_job_id=? AND status NOT IN ("delivered","cancelled")')->execute([$id]);}db()->commit();audit('refill_job_updated','refill_job',$id,$status);flash('success','Refill job updated.');redirect('index.php?page=refills&id='.$id);

            case 'save_delivery':
                $customerId=(int)($_POST['customer_id']??0);$address=trim($_POST['delivery_address']??'');$fee=max(0,decimal($_POST['delivery_fee']??0));if(!$customerId||$address===''||$fee<=0)throw new RuntimeException('Select the customer and enter the delivery address and agreed fee.');$reference='SZW-DL-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));$scheduled=($_POST['scheduled_for']??'')?date('Y-m-d H:i:s',strtotime($_POST['scheduled_for'])):null;$status=$scheduled?'scheduled':'pending';$payment=in_array($_POST['payment_status']??'',['unpaid','partial','paid'],true)?$_POST['payment_status']:'unpaid';db()->beginTransaction();db()->prepare('INSERT INTO deliveries (reference,customer_id,opportunity_id,document_id,delivery_address,area,delivery_fee,payment_status,payment_method,amount_collected,scheduled_for,driver_name,driver_phone,status,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute([$reference,$customerId,(int)($_POST['opportunity_id']??0)?:null,(int)($_POST['document_id']??0)?:null,$address,trim($_POST['area']??''),$fee,$payment,trim($_POST['payment_method']??''),max(0,decimal($_POST['amount_collected']??0)),$scheduled,trim($_POST['driver_name']??''),trim($_POST['driver_phone']??''),$status,trim($_POST['notes']??''),user()['id']]);$id=(int)db()->lastInsertId();db()->prepare('INSERT INTO delivery_events (delivery_id,event_type,event_label,notes,created_by) VALUES (? ,"created","Delivery scheduled",?,?)')->execute([$id,'Delivery fee '.number_format($fee,2),user()['id']]);db()->commit();audit('delivery_created','delivery',$id,$reference);flash('success','Delivery '.$reference.' scheduled.');redirect('index.php?page=deliveries&id='.$id);

            case 'update_delivery':
                $id=(int)($_POST['id']??0);$statuses=['pending','scheduled','out_for_delivery','delivered','failed','cancelled'];$status=(string)($_POST['status']??'');if(!$id||!in_array($status,$statuses,true))throw new RuntimeException('Choose a valid delivery status.');db()->beginTransaction();$currentStmt=db()->prepare('SELECT payment_status,payment_method,amount_collected,recipient_name,failure_reason FROM deliveries WHERE id=? FOR UPDATE');$currentStmt->execute([$id]);$current=$currentStmt->fetch();if(!$current)throw new RuntimeException('Delivery not found.');$payment=in_array($_POST['payment_status']??$current['payment_status'],['unpaid','partial','paid'],true)?($_POST['payment_status']??$current['payment_status']):$current['payment_status'];$method=trim($_POST['payment_method']??$current['payment_method']);$collected=max(0,decimal($_POST['amount_collected']??$current['amount_collected']));if($payment!=='unpaid'&&$method==='')throw new RuntimeException('Choose how the delivery payment was received.');db()->prepare('UPDATE deliveries SET status=?,driver_name=?,driver_phone=?,proof_reference=?,recipient_name=?,failure_reason=?,payment_status=?,payment_method=?,amount_collected=?,delivered_at=IF(?="delivered",NOW(),delivered_at) WHERE id=?')->execute([$status,trim($_POST['driver_name']??''),trim($_POST['driver_phone']??''),trim($_POST['proof_reference']??''),trim($_POST['recipient_name']??$current['recipient_name']),$status==='failed'?trim($_POST['failure_reason']??$current['failure_reason']):'',$payment,$method,$collected,$status,$id]);db()->prepare('INSERT INTO delivery_events (delivery_id,event_type,event_label,notes,created_by) VALUES (? ,"status",?,?,?)')->execute([$id,ucwords(str_replace('_',' ',$status)),trim($_POST['event_notes']??''),user()['id']]);db()->commit();audit('delivery_updated','delivery',$id,$status);flash('success','Delivery updated.');redirect('index.php?page=deliveries&id='.$id);

            case 'import_customers':
                $file=$_FILES['customer_csv']??null;if(!$file||$file['error']!==UPLOAD_ERR_OK)throw new RuntimeException('Choose a valid CSV file.');if((int)$file['size']>2*1024*1024)throw new RuntimeException('The CSV file must be smaller than 2 MB.');
                $handle=fopen($file['tmp_name'],'rb');if(!$handle)throw new RuntimeException('The CSV file could not be read.');$header=fgetcsv($handle);if(!$header){fclose($handle);throw new RuntimeException('The CSV file is empty.');}
                $header=array_map(fn($h)=>strtolower(trim(preg_replace('/^\xEF\xBB\xBF/','',(string)$h))),$header);if(!in_array('name',$header,true)){fclose($handle);throw new RuntimeException('The CSV must contain a name column.');}
                $allowed=['name','company_name','phone','whatsapp','email','tax_number','address','city','customer_type','area','preferred_payment','marketing_consent','next_refill_due','notes'];$created=$updated=$skipped=0;$line=1;$updateExisting=isset($_POST['update_existing']);db()->beginTransaction();
                while(($values=fgetcsv($handle))!==false){$line++;if($line>5001)throw new RuntimeException('Imports are limited to 5,000 customers at a time.');$row=[];foreach($header as $i=>$column)if(in_array($column,$allowed,true))$row[$column]=trim((string)($values[$i]??''));if(implode('',$row)==='')continue;if(($row['name']??'')===''){$skipped++;continue;}
                    $existingId=null;if(!empty($row['email'])){$find=db()->prepare('SELECT id FROM customers WHERE email=? LIMIT 1');$find->execute([$row['email']]);$existingId=$find->fetchColumn()?:null;}if(!$existingId&&!empty($row['phone'])){$find=db()->prepare('SELECT id FROM customers WHERE phone=? LIMIT 1');$find->execute([$row['phone']]);$existingId=$find->fetchColumn()?:null;}
                    $data=[];foreach($allowed as $column)$data[$column]=$row[$column]??'';
                    $type=in_array($data['customer_type'],['Household','Corporate','Restaurant / Cafe','Hotel / Lodge','Retailer','Other'],true)?$data['customer_type']:'Household';$consent=in_array(strtolower($data['marketing_consent']),['1','yes','true'],true)?1:0;$refill=preg_match('/^\d{4}-\d{2}-\d{2}$/',$data['next_refill_due'])?$data['next_refill_due']:null;
                    if($existingId){if(!$updateExisting){$skipped++;continue;}db()->prepare('UPDATE customers SET name=?,company_name=?,phone=?,whatsapp=?,email=?,tax_number=?,address=?,city=?,customer_type=?,area=?,preferred_payment=?,marketing_consent=?,next_refill_due=?,notes=?,active=1 WHERE id=?')->execute([$data['name'],$data['company_name'],$data['phone'],$data['whatsapp'],$data['email'],$data['tax_number'],$data['address'],$data['city'],$type,$data['area'],$data['preferred_payment'],$consent,$refill,$data['notes'],$existingId]);$updated++;}
                    else{db()->prepare('INSERT INTO customers (name,company_name,phone,whatsapp,email,tax_number,address,city,customer_type,area,preferred_payment,marketing_consent,next_refill_due,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute([$data['name'],$data['company_name'],$data['phone'],$data['whatsapp'],$data['email'],$data['tax_number'],$data['address'],$data['city'],$type,$data['area'],$data['preferred_payment'],$consent,$refill,$data['notes']]);$created++;}
                }
                fclose($handle);db()->commit();audit('customers_imported','customer',null,"Created {$created}; updated {$updated}; skipped {$skipped}");flash('success',"Customer import complete: {$created} created, {$updated} updated, {$skipped} skipped.");redirect('index.php?page=customers');

            case 'import_products':
                $allowed=['sku','name','description','category','unit_price','cost_price','tax_rate','stock_quantity','track_stock','reorder_level'];$rows=read_csv_import('products_csv',['name'],$allowed);$update=isset($_POST['update_existing']);$created=$updated=$skipped=0;db()->beginTransaction();foreach($rows as $row){$sku=$row['sku']??'';$name=$row['name']??'';if($name===''){$skipped++;continue;}$find=$sku!==''?db()->prepare('SELECT id,stock_quantity FROM products WHERE sku=? LIMIT 1'):db()->prepare('SELECT id,stock_quantity FROM products WHERE name=? LIMIT 1');$find->execute([$sku!==''?$sku:$name]);$existing=$find->fetch();$values=[$sku?:null,$name,$row['description']??'',$row['category']?:'Other',max(0,decimal($row['unit_price']??0)),max(0,decimal($row['cost_price']??0)),max(0,decimal($row['tax_rate']??0)),decimal($row['stock_quantity']??0),in_array(strtolower($row['track_stock']??'1'),['1','yes','true'],true)?1:0,max(0,decimal($row['reorder_level']??0))];if($existing){if(!$update){$skipped++;continue;}$values[]=$existing['id'];db()->prepare('UPDATE products SET sku=?,name=?,description=?,category=?,unit_price=?,cost_price=?,tax_rate=?,stock_quantity=?,track_stock=?,reorder_level=?,active=1 WHERE id=?')->execute($values);$difference=(float)$values[7]-(float)$existing['stock_quantity'];if(abs($difference)>.0001)db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,notes,created_by) VALUES (?,"adjustment",?,"CSV stock update",?)')->execute([$existing['id'],$difference,user()['id']]);$updated++;}else{db()->prepare('INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level) VALUES (?,?,?,?,?,?,?,?,?,?)')->execute($values);$id=(int)db()->lastInsertId();if((float)$values[7]!==0.0)db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,notes,created_by) VALUES (?,"opening",?,"CSV opening stock",?)')->execute([$id,$values[7],user()['id']]);$created++;}}db()->commit();audit('products_imported','product',null,"Created {$created}; updated {$updated}; skipped {$skipped}");flash('success',"Product import complete: {$created} created, {$updated} updated, {$skipped} skipped.");redirect('index.php?page=products');

            case 'import_suppliers':
                $allowed=['name','contact_name','email','phone','tax_number','address','notes'];$rows=read_csv_import('suppliers_csv',['name'],$allowed);$update=isset($_POST['update_existing']);$created=$updated=$skipped=0;db()->beginTransaction();foreach($rows as $row){$name=$row['name']??'';if($name===''){$skipped++;continue;}$find=db()->prepare('SELECT id FROM suppliers WHERE name=? LIMIT 1');$find->execute([$name]);$id=$find->fetchColumn();$values=[$name,$row['contact_name']??'',$row['email']??'',$row['phone']??'',$row['tax_number']??'',$row['address']??'',$row['notes']??''];if($id){if(!$update){$skipped++;continue;}$values[]=$id;db()->prepare('UPDATE suppliers SET name=?,contact_name=?,email=?,phone=?,tax_number=?,address=?,notes=?,active=1 WHERE id=?')->execute($values);$updated++;}else{db()->prepare('INSERT INTO suppliers (name,contact_name,email,phone,tax_number,address,notes) VALUES (?,?,?,?,?,?,?)')->execute($values);$created++;}}db()->commit();audit('suppliers_imported','supplier',null,"Created {$created}; updated {$updated}; skipped {$skipped}");flash('success',"Supplier import complete: {$created} created, {$updated} updated, {$skipped} skipped.");redirect('index.php?page=suppliers');

            case 'import_bills':
                $allowed=['supplier_name','bill_number','bill_date','due_date','description','currency_code','total','reference','notes','amount_paid','payment_date','payment_account'];$rows=read_csv_import('bills_csv',['supplier_name','bill_number','total'],$allowed);$created=$skipped=$suppliersCreated=0;db()->beginTransaction();foreach($rows as $row){$supplierName=$row['supplier_name']??'';$number=$row['bill_number']??'';$total=max(0,decimal($row['total']??0));if($supplierName===''||$number===''||$total<=0){$skipped++;continue;}$find=db()->prepare('SELECT id FROM suppliers WHERE name=? LIMIT 1');$find->execute([$supplierName]);$supplierId=$find->fetchColumn();if(!$supplierId){db()->prepare('INSERT INTO suppliers (name) VALUES (?)')->execute([$supplierName]);$supplierId=(int)db()->lastInsertId();$suppliersCreated++;}$dupe=db()->prepare('SELECT id FROM bills WHERE supplier_id=? AND number=?');$dupe->execute([$supplierId,$number]);if($dupe->fetchColumn()){$skipped++;continue;}$billDate=preg_match('/^\d{4}-\d{2}-\d{2}$/',$row['bill_date']??'')?$row['bill_date']:date('Y-m-d');$dueDate=preg_match('/^\d{4}-\d{2}-\d{2}$/',$row['due_date']??'')?$row['due_date']:$billDate;$currency=preg_match('/^[A-Z]{3}$/',strtoupper($row['currency_code']??''))?strtoupper($row['currency_code']):'USD';$paid=min($total,max(0,decimal($row['amount_paid']??0)));$status=$paid>=$total-.01?'paid':($paid>0?'partial':'unpaid');db()->prepare('INSERT INTO bills (supplier_id,number,bill_date,due_date,description,currency_code,total,status,reference,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?)')->execute([$supplierId,$number,$billDate,$dueDate,$row['description']??'Imported supplier bill',$currency,$total,$status,$row['reference']??'',$row['notes']??'',user()['id']]);$billId=(int)db()->lastInsertId();if($paid>0){$accountId=null;if(($row['payment_account']??'')!==''){$a=db()->prepare('SELECT id FROM payment_accounts WHERE name=? AND currency_code=? LIMIT 1');$a->execute([$row['payment_account'],$currency]);$accountId=$a->fetchColumn()?:null;}$paymentDate=preg_match('/^\d{4}-\d{2}-\d{2}$/',$row['payment_date']??'')?$row['payment_date']:$billDate;db()->prepare('INSERT INTO bill_payments (bill_id,account_id,payment_date,amount,reference,notes,created_by) VALUES (?,?,?,?,?,"Imported payment",?)')->execute([$billId,$accountId,$paymentDate,$paid,$row['reference']??'',user()['id']]);}$created++;}db()->commit();audit('bills_imported','bill',null,"Created {$created}; skipped {$skipped}");flash('success',"Bill import complete: {$created} created, {$skipped} skipped, {$suppliersCreated} suppliers added.");redirect('index.php?page=bills');

            case 'import_expenses':
                $allowed=['expense_date','category','supplier','description','amount','reference'];$rows=read_csv_import('expenses_csv',['expense_date','category','description','amount'],$allowed);$created=$skipped=0;db()->beginTransaction();$insert=db()->prepare('INSERT INTO expenses (expense_date,category,supplier,description,amount,reference,created_by) VALUES (?,?,?,?,?,?,?)');foreach($rows as $row){$date=preg_match('/^\d{4}-\d{2}-\d{2}$/',$row['expense_date']??'')?$row['expense_date']:'';$amount=max(0,decimal($row['amount']??0));if($date===''||($row['category']??'')===''||($row['description']??'')===''||$amount<=0){$skipped++;continue;}$insert->execute([$date,$row['category'],$row['supplier']??'',$row['description'],$amount,$row['reference']??'',user()['id']]);$created++;}db()->commit();audit('expenses_imported','expense',null,"Created {$created}; skipped {$skipped}");flash('success',"Expense import complete: {$created} created, {$skipped} skipped.");redirect('index.php?page=expenses');

            case 'save_product':
                $id=(int)($_POST['id']??0); $name=trim($_POST['name']??''); if($name==='') throw new RuntimeException('Product name is required.');
                $values=[trim($_POST['sku']??'')?:null,$name,trim($_POST['description']??''),trim($_POST['category']??'Other'),max(0,decimal($_POST['unit_price']??0)),max(0,decimal($_POST['cost_price']??0)),max(0,decimal($_POST['tax_rate']??0)),decimal($_POST['stock_quantity']??0),isset($_POST['track_stock'])?1:0,max(0,decimal($_POST['reorder_level']??0))];
                if($id){$values[]=$id;db()->prepare('UPDATE products SET sku=?,name=?,description=?,category=?,unit_price=?,cost_price=?,tax_rate=?,stock_quantity=?,track_stock=?,reorder_level=? WHERE id=?')->execute($values);}else{db()->prepare('INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level) VALUES (?,?,?,?,?,?,?,?,?,?)')->execute($values);$id=(int)db()->lastInsertId();}
                audit('product_saved','product',$id);flash('success','Product saved.');redirect('index.php?page=products');

            case 'sync_website_products':
                if(!in_array((string)(user()['role']??''),['owner','admin'],true))throw new RuntimeException('Only an owner or administrator can synchronise the catalogue.');
                $catalogueUrl='https://sodazw.co.zw/products.json';
                if(!function_exists('curl_init'))throw new RuntimeException('Website synchronisation needs the PHP cURL extension enabled in cPanel.');
                $body='';$tooLarge=false;$ch=curl_init($catalogueUrl);
                curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>false,CURLOPT_FOLLOWLOCATION=>false,CURLOPT_CONNECTTIMEOUT=>5,CURLOPT_TIMEOUT=>12,CURLOPT_PROTOCOLS=>CURLPROTO_HTTPS,CURLOPT_USERAGENT=>'SodaZW-Billing-Catalogue/1.0',CURLOPT_WRITEFUNCTION=>static function($curl,string $chunk)use(&$body,&$tooLarge):int{if(strlen($body)+strlen($chunk)>262144){$tooLarge=true;return 0;}$body.=$chunk;return strlen($chunk);}]);
                $ok=curl_exec($ch);$status=(int)curl_getinfo($ch,CURLINFO_RESPONSE_CODE);$curlError=curl_error($ch);curl_close($ch);
                if(!$ok||$tooLarge||$status!==200)throw new RuntimeException('The website product list could not be downloaded'.($curlError!==''?': '.$curlError:'.').' Confirm products.json is uploaded to sodazw.co.zw.');
                $catalogue=json_decode($body,true,64,JSON_THROW_ON_ERROR);if(!is_array($catalogue)||($catalogue['currency']??'')!=='USD'||!is_array($catalogue['products']??null))throw new RuntimeException('The website product list has an invalid format.');
                if(count($catalogue['products'])<1||count($catalogue['products'])>100)throw new RuntimeException('The website product list must contain between 1 and 100 products.');
                $allowedCategories=['Sparkling Water Machines','CO2 Cylinders','Bottles','Flavours & Syrups','Other'];$created=$updated=$skipped=0;db()->beginTransaction();
                $find=db()->prepare('SELECT id FROM products WHERE sku=? LIMIT 1');$insert=db()->prepare('INSERT INTO products (sku,name,description,category,unit_price,cost_price,tax_rate,stock_quantity,track_stock,reorder_level,active) VALUES (?,?,?,?,?,0,0,0,?,0,?)');$update=db()->prepare('UPDATE products SET name=?,description=?,category=?,unit_price=?,active=? WHERE id=?');
                foreach($catalogue['products'] as $item){$sku=strtoupper(trim((string)($item['sku']??'')));$name=trim((string)($item['name']??''));$description=trim((string)($item['description']??''));$category=(string)($item['category']??'Other');$price=$item['price']??null;$active=filter_var($item['active']??true,FILTER_VALIDATE_BOOL)?1:0;$track=filter_var($item['track_stock']??true,FILTER_VALIDATE_BOOL)?1:0;if(!preg_match('/^SZW-[A-Z0-9-]{2,70}$/',$sku)||$name===''||strlen($name)>160||!is_numeric($price)||(float)$price<0||(float)$price>100000){$skipped++;continue;}if(!in_array($category,$allowedCategories,true))$category='Other';$find->execute([$sku]);$id=$find->fetchColumn();if($id){$update->execute([substr($name,0,160),substr($description,0,1000),$category,round((float)$price,2),$active,$id]);$updated++;}else{$insert->execute([$sku,substr($name,0,160),substr($description,0,1000),$category,round((float)$price,2),$track,$active]);$created++;}}
                db()->commit();audit('website_catalogue_synced','product',null,"Created {$created}; updated {$updated}; skipped {$skipped}");flash('success',"Website catalogue synchronised: {$created} added, {$updated} updated, {$skipped} skipped. Stock and cost prices were preserved.");redirect('index.php?page=products');

            case 'bulk_set_stock_counts':
                $counts=is_array($_POST['stock_counts']??null)?$_POST['stock_counts']:[];if(!$counts||count($counts)>100)throw new RuntimeException('Enter at least one valid stock quantity.');$changed=0;db()->beginTransaction();$find=db()->prepare('SELECT id,name,stock_quantity FROM products WHERE id=? AND active=1 FOR UPDATE');$update=db()->prepare('UPDATE products SET stock_quantity=?,track_stock=1 WHERE id=?');$movement=db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,notes,created_by) VALUES (? ,"adjustment",?,"Bulk physical stock count",?)');foreach($counts as $productId=>$rawActual){$productId=(int)$productId;if(!$productId||$rawActual===''||!is_numeric($rawActual)||(float)$rawActual<0)continue;$actual=round((float)$rawActual,2);$find->execute([$productId]);$product=$find->fetch();if(!$product)continue;$difference=round($actual-(float)$product['stock_quantity'],2);$update->execute([$actual,$productId]);if(abs($difference)>.0001){$movement->execute([$productId,$difference,user()['id']]);$changed++;}}db()->commit();audit('bulk_stock_count_saved','product',null,$changed.' quantities changed');flash('success',$changed?$changed.' stock quantities updated.':'Stock count saved; no quantities changed.');redirect('index.php?page=products');

            case 'set_stock_count':
                $productId=(int)($_POST['product_id']??0);$actual=decimal($_POST['actual_stock']??-1);if(!$productId||$actual<0)throw new RuntimeException('Enter the actual stock quantity currently on hand.');db()->beginTransaction();$p=db()->prepare('SELECT name,stock_quantity FROM products WHERE id=? AND active=1 FOR UPDATE');$p->execute([$productId]);$product=$p->fetch();if(!$product)throw new RuntimeException('Product not found.');$difference=round($actual-(float)$product['stock_quantity'],2);db()->prepare('UPDATE products SET stock_quantity=?,track_stock=1 WHERE id=?')->execute([$actual,$productId]);if(abs($difference)>.0001)db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,notes,created_by) VALUES (? ,"adjustment",?,"Direct stock count",?)')->execute([$productId,$difference,user()['id']]);db()->commit();audit('stock_count_set','product',$productId,(string)$actual);flash('success',$product['name'].' stock is now '.$actual.'.');redirect('index.php?page=products');

            case 'adjust_stock':
                $productId=(int)($_POST['product_id']??0);$change=decimal($_POST['quantity_change']??0);$movementType=in_array($_POST['movement_type']??'', ['opening','purchase','return','adjustment'],true)?$_POST['movement_type']:'adjustment';if(!$productId||abs($change)<.0001)throw new RuntimeException('Select a product and enter a stock quantity change.');db()->beginTransaction();$p=db()->prepare('SELECT stock_quantity FROM products WHERE id=? AND active=1 FOR UPDATE');$p->execute([$productId]);$current=$p->fetchColumn();if($current===false)throw new RuntimeException('Product not found.');$new=(float)$current+$change;if($new<0)throw new RuntimeException('This adjustment would make stock negative.');db()->prepare('UPDATE products SET stock_quantity=?,track_stock=1 WHERE id=?')->execute([$new,$productId]);db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,notes,created_by) VALUES (?,?,?,?,?)')->execute([$productId,$movementType,$change,trim($_POST['notes']??''),user()['id']]);db()->commit();audit('stock_adjusted','product',$productId,(string)$change);flash('success','Stock level updated.');redirect('index.php?page=products');

            case 'update_web_order':
                $id=(int)($_POST['id']??0);$status=(string)($_POST['status']??'');
                if(!$id||!in_array($status,['new','contacted','confirmed','fulfilled','cancelled'],true))throw new RuntimeException('Choose a valid website order status.');
                $check=db()->prepare('SELECT status,invoice_id FROM web_orders WHERE id=?');$check->execute([$id]);$current=$check->fetch();
                if(!$current)throw new RuntimeException('Website order not found.');
                if($status==='fulfilled'&&!$current['invoice_id'])throw new RuntimeException('Create the invoice before marking this order fulfilled.');
                if(in_array($current['status'],['invoiced','fulfilled'],true)&&$status!=='fulfilled')throw new RuntimeException('This order has already been converted to an invoice.');
                db()->prepare('UPDATE web_orders SET status=? WHERE id=?')->execute([$status,$id]);$crmStage=['new'=>'new_enquiry','contacted'=>'contacted','confirmed'=>'order_confirmed','fulfilled'=>'completed','cancelled'=>'lost'][$status]??'new_enquiry';db()->prepare('UPDATE crm_opportunities SET stage=? WHERE web_order_id=?')->execute([$crmStage,$id]);audit('website_order_status','web_order',$id,$status);flash('success','Website order and CRM pipeline updated.');redirect('index.php?page=web-orders&id='.$id);

            case 'convert_web_order':
                $id=(int)($_POST['id']??0);if(!$id)throw new RuntimeException('Website order not found.');db()->beginTransaction();
                $find=db()->prepare('SELECT * FROM web_orders WHERE id=? FOR UPDATE');$find->execute([$id]);$order=$find->fetch();if(!$order)throw new RuntimeException('Website order not found.');
                if($order['invoice_id']){$invoiceId=(int)$order['invoice_id'];db()->commit();redirect('index.php?page=document-view&id='.$invoiceId);}
                if($order['status']==='cancelled')throw new RuntimeException('A cancelled order cannot be invoiced.');
                $deliveryFee=$order['fulfilment_method']==='Delivery'?max(0,decimal($_POST['delivery_fee']??0)):0;if($order['fulfilment_method']==='Delivery'&&$deliveryFee<=0)throw new RuntimeException('Enter the delivery fee agreed for the customer location before creating the invoice.');$invoiceTotal=round((float)$order['subtotal']+$deliveryFee,2);
                $itemQuery=db()->prepare('SELECT wi.*,COALESCE(p.cost_price,0) unit_cost FROM web_order_items wi LEFT JOIN products p ON p.id=wi.product_id WHERE wi.web_order_id=? ORDER BY wi.id');$itemQuery->execute([$id]);$items=$itemQuery->fetchAll();if(!$items)throw new RuntimeException('This website order has no items.');
                $customerId=null;if($order['email']){$customerLookup=db()->prepare('SELECT id FROM customers WHERE active=1 AND email=? LIMIT 1');$customerLookup->execute([$order['email']]);$customerId=$customerLookup->fetchColumn()?:null;}
                if(!$customerId){$customerLookup=db()->prepare('SELECT id FROM customers WHERE active=1 AND (phone=? OR whatsapp=?) LIMIT 1');$customerLookup->execute([$order['phone'],$order['phone']]);$customerId=$customerLookup->fetchColumn()?:null;}
                if(!$customerId){db()->prepare('INSERT INTO customers (name,email,phone,whatsapp,address,city,notes) VALUES (?,?,?,?,?,"Harare",?)')->execute([$order['customer_name'],$order['email']?:null,$order['phone'],$order['phone'],$order['delivery_address']?:null,'Created from website order '.$order['order_number']]);$customerId=(int)db()->lastInsertId();}
                $number=reserve_number('invoice');$issue=date('Y-m-d');$due=date('Y-m-d',strtotime('+'.max(0,(int)(settings()['default_due_days']??7)).' days'));$shareToken=bin2hex(random_bytes(32));$notes=trim('Website order '.$order['order_number'].($order['payment_preference']?' | Preferred payment: '.$order['payment_preference']:'').($order['notes']?' | '.$order['notes']:''));
                $document=db()->prepare('INSERT INTO documents (type,number,customer_id,issue_date,due_date,status,currency_code,subtotal,discount_type,discount_value,discount_total,tax_total,delivery_fee,total,notes,terms,customer_reference,order_source,fulfilment_method,delivery_address,share_token,created_by) VALUES ("invoice",?,?,?,? ,"draft",?,? ,"fixed",0,0,0,?,?,?, ?,?,"Website",?,?,?,?)');
                $document->execute([$number,$customerId,$issue,$due,$order['currency_code'],$order['subtotal'],$deliveryFee,$invoiceTotal,$notes,(string)(settings()['invoice_terms']??''),$order['order_number'],$order['fulfilment_method'],$order['delivery_address'],$shareToken,user()['id']]);$invoiceId=(int)db()->lastInsertId();
                $itemInsert=db()->prepare('INSERT INTO document_items (document_id,product_id,description,quantity,unit_price,unit_cost,tax_rate,line_subtotal,line_tax,line_total,sort_order) VALUES (?,?,?,?,?,?,0,?,0,?,?)');foreach($items as $i=>$item)$itemInsert->execute([$invoiceId,$item['product_id'],$item['product_name'],$item['quantity'],$item['unit_price'],$item['unit_cost'],$item['line_total'],$item['line_total'],$i]);
                db()->prepare('UPDATE web_orders SET customer_id=?,invoice_id=?,delivery_fee=?,total=?,status="invoiced" WHERE id=?')->execute([$customerId,$invoiceId,$deliveryFee,$invoiceTotal,$id]);$crm=db()->prepare('SELECT id FROM crm_opportunities WHERE web_order_id=?');$crm->execute([$id]);if($crmId=$crm->fetchColumn()){db()->prepare('UPDATE crm_opportunities SET customer_id=?,document_id=?,stage="invoiced",value=?,currency_code=? WHERE id=?')->execute([$customerId,$invoiceId,$invoiceTotal,$order['currency_code'],$crmId]);}else{db()->prepare('INSERT INTO crm_opportunities (customer_id,web_order_id,document_id,title,stage,source,value,currency_code,assigned_to,notes,created_by) VALUES (?,?,?, ?,"invoiced","Website",?,?,?,?,?)')->execute([$customerId,$id,$invoiceId,'Website order '.$order['order_number'],$invoiceTotal,$order['currency_code'],user()['id'],'Created automatically from '.$order['order_number'],user()['id']]);}if($order['fulfilment_method']==='Delivery'){$deliveryReference='SZW-DL-'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(2)));db()->prepare('INSERT INTO deliveries (reference,customer_id,document_id,delivery_address,area,delivery_fee,status,notes,created_by) SELECT ?,?,?,?,c.area,?,"pending",?,? FROM customers c WHERE c.id=?')->execute([$deliveryReference,$customerId,$invoiceId,$order['delivery_address'],$deliveryFee,'Website order '.$order['order_number'],user()['id'],$customerId]);}db()->commit();audit('website_order_invoiced','web_order',$id,$number);flash('success','Draft invoice '.$number.' created and added to the CRM pipeline.');redirect('index.php?page=document-view&id='.$invoiceId);

            case 'save_document':
                $id=(int)($_POST['id']??0);$type=in_array($_POST['type']??'', ['quotation','invoice'],true)?$_POST['type']:'invoice';$customerId=(int)($_POST['customer_id']??0);
                if(!$customerId)throw new RuntimeException('Select a customer.');
                $discountType=($_POST['discount_type']??'fixed')==='percent'?'percent':'fixed';$discountValue=max(0,decimal($_POST['discount_value']??0));$delivery=max(0,decimal($_POST['delivery_fee']??0));$hasOperations=isset($_POST['order_source']);$orderSource=in_array($_POST['order_source']??'', ['WhatsApp','Website','Walk-in','Phone','Social media','Other'],true)?$_POST['order_source']:'WhatsApp';$fulfilment=in_array($_POST['fulfilment_method']??'', ['Pickup','Delivery'],true)?$_POST['fulfilment_method']:'Pickup';$deliveryAddress=trim($_POST['delivery_address']??'');
                if($fulfilment==='Delivery'&&$delivery<=0)throw new RuntimeException('Enter the delivery fee agreed for the customer location.');$totals=document_totals($_POST['items']??[],$discountType,$discountValue,$delivery);if(!$totals['items'])throw new RuntimeException('Add at least one line item.');
                db()->beginTransaction();
                if($id){$check=db()->prepare('SELECT * FROM documents WHERE id=? AND status="draft"');$check->execute([$id]);$existing=$check->fetch();if(!$existing)throw new RuntimeException('Only draft documents can be edited.');$type=$existing['type'];$number=$existing['number'];if(!$hasOperations){$orderSource=$existing['order_source'];$fulfilment=$existing['fulfilment_method'];$deliveryAddress=$existing['delivery_address']??'';}
                    $stmt=db()->prepare('UPDATE documents SET customer_id=?,issue_date=?,due_date=?,currency_code=?,subtotal=?,discount_type=?,discount_value=?,discount_total=?,tax_total=?,delivery_fee=?,total=?,notes=?,terms=?,order_source=?,fulfilment_method=?,delivery_address=? WHERE id=?');
                    $stmt->execute([$customerId,$_POST['issue_date'],$_POST['due_date']?:null,settings()['currency_code'],$totals['subtotal'],$discountType,$discountValue,$totals['discount_total'],$totals['tax_total'],$delivery,$totals['total'],trim($_POST['notes']??''),trim($_POST['terms']??''),$orderSource,$fulfilment,$deliveryAddress,$id]);db()->prepare('DELETE FROM document_items WHERE document_id=?')->execute([$id]);
                }else{$number=reserve_number($type);$stmt=db()->prepare('INSERT INTO documents (type,number,customer_id,issue_date,due_date,status,currency_code,subtotal,discount_type,discount_value,discount_total,tax_total,delivery_fee,total,notes,terms,order_source,fulfilment_method,delivery_address,created_by) VALUES (?,?,?,?,?,"draft",?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
                    $stmt->execute([$type,$number,$customerId,$_POST['issue_date'],$_POST['due_date']?:null,settings()['currency_code'],$totals['subtotal'],$discountType,$discountValue,$totals['discount_total'],$totals['tax_total'],$delivery,$totals['total'],trim($_POST['notes']??''),trim($_POST['terms']??''),$orderSource,$fulfilment,$deliveryAddress,user()['id']]);$id=(int)db()->lastInsertId();}
                db()->prepare('UPDATE documents SET customer_reference=?,order_source=?,fulfilment_method=?,delivery_address=? WHERE id=?')->execute([trim($_POST['customer_reference']??''),$orderSource,$fulfilment,$deliveryAddress,$id]);if($type==='invoice')db()->prepare('UPDATE documents SET share_token=COALESCE(share_token,?) WHERE id=?')->execute([bin2hex(random_bytes(32)),$id]);$itemStmt=db()->prepare('INSERT INTO document_items (document_id,product_id,description,quantity,unit_price,unit_cost,tax_rate,line_subtotal,line_tax,line_total,sort_order) VALUES (?,?,?,?,?,?,?,?,?,?,?)');$costStmt=db()->prepare('SELECT cost_price FROM products WHERE id=?');
                foreach($totals['items'] as $i=>$item){$cost=0;if($item['product_id']){$costStmt->execute([$item['product_id']]);$cost=(float)($costStmt->fetchColumn()?:0);}$itemStmt->execute([$id,$item['product_id'],$item['description'],$item['quantity'],$item['unit_price'],$cost,$item['tax_rate'],$item['line_subtotal'],$item['line_tax'],$item['line_total'],$i]);}
                db()->commit();audit('document_saved',$type,$id,$number);flash('success',document_label($type).' '.$number.' saved.');redirect('index.php?page=document-view&id='.$id);

            case 'import_invoices':
                require_role(['owner','admin']);
                $file=$_FILES['invoice_csv']??null;if(!$file||$file['error']!==UPLOAD_ERR_OK)throw new RuntimeException('Choose a valid invoice CSV file.');if((int)$file['size']>4*1024*1024)throw new RuntimeException('The invoice CSV must be smaller than 4 MB.');
                $handle=fopen($file['tmp_name'],'rb');if(!$handle)throw new RuntimeException('The invoice CSV could not be read.');$header=fgetcsv($handle);if(!$header){fclose($handle);throw new RuntimeException('The invoice CSV is empty.');}$header=array_map(fn($h)=>strtolower(trim(preg_replace('/^\xEF\xBB\xBF/','',(string)$h))),$header);
                foreach(['invoice_number','customer_name','item_description','unit_price'] as $column)if(!in_array($column,$header,true)){fclose($handle);throw new RuntimeException("The CSV must contain a {$column} column.");}
                $allowed=['invoice_number','issue_date','due_date','status','customer_name','company_name','phone','whatsapp','email','address','city','item_description','quantity','unit_price','tax_rate','discount_type','discount_value','delivery_fee','notes','amount_paid','payment_date','payment_method','payment_reference'];$groups=[];$line=1;
                while(($values=fgetcsv($handle))!==false){$line++;if($line>10001)throw new RuntimeException('Invoice imports are limited to 10,000 rows.');$row=[];foreach($header as $i=>$column)if(in_array($column,$allowed,true))$row[$column]=trim((string)($values[$i]??''));if(implode('',$row)==='')continue;$number=$row['invoice_number']??'';if($number===''||($row['customer_name']??'')===''||($row['item_description']??'')==='')continue;if(!isset($groups[$number]))$groups[$number]=['head'=>$row,'items'=>[]];$groups[$number]['items'][]=['description'=>$row['item_description'],'quantity'=>$row['quantity']?:1,'unit_price'=>$row['unit_price']?:0,'tax_rate'=>$row['tax_rate']?:0,'product_id'=>null];}
                fclose($handle);if(!$groups)throw new RuntimeException('No valid invoice rows were found.');$created=$skipped=$customersCreated=0;db()->beginTransaction();
                foreach($groups as $sourceNumber=>$group){$exists=db()->prepare('SELECT id FROM documents WHERE type="invoice" AND (number=? OR customer_reference=?) LIMIT 1');$exists->execute([$sourceNumber,$sourceNumber]);if($exists->fetchColumn()){$skipped++;continue;}$r=$group['head'];$customerId=null;
                    if(!empty($r['email'])){$find=db()->prepare('SELECT id FROM customers WHERE email=? LIMIT 1');$find->execute([$r['email']]);$customerId=$find->fetchColumn()?:null;}if(!$customerId&&!empty($r['phone'])){$find=db()->prepare('SELECT id FROM customers WHERE phone=? LIMIT 1');$find->execute([$r['phone']]);$customerId=$find->fetchColumn()?:null;}if(!$customerId){$find=db()->prepare('SELECT id FROM customers WHERE name=? LIMIT 1');$find->execute([$r['customer_name']]);$customerId=$find->fetchColumn()?:null;}
                    if(!$customerId){db()->prepare('INSERT INTO customers (name,company_name,phone,whatsapp,email,address,city) VALUES (?,?,?,?,?,?,?)')->execute([$r['customer_name'],$r['company_name']??'',$r['phone']??'',$r['whatsapp']??'',$r['email']??'',$r['address']??'',$r['city']??'']);$customerId=(int)db()->lastInsertId();$customersCreated++;}
                    $discountType=($r['discount_type']??'fixed')==='percent'?'percent':'fixed';$discountValue=max(0,decimal($r['discount_value']??0));$delivery=max(0,decimal($r['delivery_fee']??0));$totals=document_totals($group['items'],$discountType,$discountValue,$delivery);$issue=preg_match('/^\d{4}-\d{2}-\d{2}$/',$r['issue_date']??'')?$r['issue_date']:date('Y-m-d');$due=preg_match('/^\d{4}-\d{2}-\d{2}$/',$r['due_date']??'')?$r['due_date']:date('Y-m-d',strtotime($issue.' +'.(int)settings()['default_due_days'].' days'));$paid=min($totals['total'],max(0,decimal($r['amount_paid']??0)));$requested=$r['status']??'sent';$status=$paid>=$totals['total']&&$totals['total']>0?'paid':($paid>0?'partial':($due<date('Y-m-d')?'overdue':(in_array($requested,['draft','sent','cancelled'],true)?$requested:'sent')));
                    $number=reserve_unique_number('invoice');$sourceNote='Original source invoice: '.$sourceNumber;$notes=trim((string)($r['notes']??''));if(stripos($notes,$sourceNote)===false)$notes=trim($notes."\n".$sourceNote);
                    $stmt=db()->prepare('INSERT INTO documents (type,number,customer_id,issue_date,due_date,status,currency_code,subtotal,discount_type,discount_value,discount_total,tax_total,delivery_fee,total,notes,terms,customer_reference,order_source,fulfilment_method,share_token,created_by) VALUES ("invoice",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');$stmt->execute([$number,$customerId,$issue,$due,$status,settings()['currency_code'],$totals['subtotal'],$discountType,$discountValue,$totals['discount_total'],$totals['tax_total'],$delivery,$totals['total'],$notes,(string)(settings()['invoice_terms']??''),$sourceNumber,'Historical import','Pickup',bin2hex(random_bytes(32)),user()['id']]);$invoiceId=(int)db()->lastInsertId();
                    $itemStmt=db()->prepare('INSERT INTO document_items (document_id,product_id,description,quantity,unit_price,tax_rate,line_subtotal,line_tax,line_total,sort_order) VALUES (?,?,?,?,?,?,?,?,?,?)');foreach($totals['items'] as $i=>$item)$itemStmt->execute([$invoiceId,null,$item['description'],$item['quantity'],$item['unit_price'],$item['tax_rate'],$item['line_subtotal'],$item['line_tax'],$item['line_total'],$i]);
                    if($paid>0){$paymentDate=preg_match('/^\d{4}-\d{2}-\d{2}$/',$r['payment_date']??'')?$r['payment_date']:$issue;db()->prepare('INSERT INTO payments (invoice_id,payment_date,amount,method,reference,notes,created_by) VALUES (?,?,?,?,?,"Imported payment",?)')->execute([$invoiceId,$paymentDate,$paid,($r['payment_method']??'')?:'Imported payment',$r['payment_reference']??'',user()['id']]);}$created++;}
                db()->commit();audit('invoices_imported','invoice',null,"Created {$created}; skipped {$skipped}; customers {$customersCreated}; standard numbering enabled");flash('success',"Invoice import complete: {$created} standard Soda ZW invoices created, {$skipped} duplicates skipped, {$customersCreated} new customers added.");redirect('index.php?page=invoices');

            case 'normalise_imported_invoices':
                require_role(['owner']);$updated=$renumbered=0;db()->beginTransaction();
                $imports=db()->query('SELECT d.id,d.number,d.customer_reference,d.notes,d.terms FROM documents d WHERE d.type="invoice" AND (d.terms="Imported historical invoice" OR d.order_source="Historical import" OR EXISTS (SELECT 1 FROM document_items i WHERE i.document_id=d.id AND i.description="Historical Soda ZW sale imported from Wave")) ORDER BY d.issue_date,d.id FOR UPDATE')->fetchAll();
                $prefix=(string)(settings()['invoice_prefix']??'SZW-INV-');$standardTerms=(string)(settings()['invoice_terms']??'');
                foreach($imports as $invoice){$source=trim((string)$invoice['customer_reference']);if($source==='')$source=(string)$invoice['number'];$number=(string)$invoice['number'];if(!str_starts_with($number,$prefix)){$number=reserve_unique_number('invoice');$renumbered++;}$sourceNote='Original source invoice: '.$source;$notes=trim((string)$invoice['notes']);if(stripos($notes,$sourceNote)===false)$notes=trim($notes."\n".$sourceNote);$terms=((string)$invoice['terms']==='Imported historical invoice'||trim((string)$invoice['terms'])==='')?$standardTerms:(string)$invoice['terms'];db()->prepare('UPDATE documents SET number=?,customer_reference=?,notes=?,terms=?,order_source="Historical import",fulfilment_method=COALESCE(NULLIF(fulfilment_method,""),"Pickup"),share_token=COALESCE(share_token,?) WHERE id=?')->execute([$number,$source,$notes,$terms,bin2hex(random_bytes(32)),$invoice['id']]);update_invoice_status((int)$invoice['id']);$updated++;}
                db()->commit();audit('imported_invoices_normalised','invoice',null,"Updated {$updated}; renumbered {$renumbered}");flash('success',"{$updated} imported invoices now follow the Soda ZW billing standard. {$renumbered} received standard Soda ZW invoice numbers.");redirect('index.php?page=imports');

            case 'set_document_status':
                $id=(int)($_POST['id']??0);$status=(string)($_POST['status']??'');if(!$id||$status==='')throw new RuntimeException('Choose a valid document status.');db()->beginTransaction();$documentStmt=db()->prepare('SELECT type,status,stock_processed_at FROM documents WHERE id=? FOR UPDATE');$documentStmt->execute([$id]);$document=$documentStmt->fetch();if(!$document)throw new RuntimeException('Document not found.');$current=(string)$document['status'];
                if($status===$current){db()->commit();flash('success','Document status is already '.str_replace('_',' ',$status).'.');redirect('index.php?page=document-view&id='.$id);}
                if($document['type']==='quotation'){$transitions=['draft'=>['sent','cancelled'],'sent'=>['accepted','rejected','cancelled'],'accepted'=>['cancelled'],'rejected'=>[],'cancelled'=>[]];}else{$transitions=['draft'=>['sent','cancelled'],'sent'=>['cancelled'],'overdue'=>['cancelled'],'partial'=>[],'paid'=>[],'cancelled'=>[]];}
                if(!in_array($status,$transitions[$current]??[],true))throw new RuntimeException('A '.str_replace('_',' ',$current).' '.str_replace('_',' ',$document['type']).' cannot be moved to '.str_replace('_',' ',$status).'.');
                if($document['type']==='quotation'){$conversionStmt=db()->prepare('SELECT invoice_id FROM quotation_conversions WHERE quotation_id=? LIMIT 1');$conversionStmt->execute([$id]);if($conversionStmt->fetchColumn())throw new RuntimeException('A converted quotation is locked and cannot change status.');}
                if($document['type']==='invoice'&&$status==='cancelled'){if(invoice_payment_total($id)>.009)throw new RuntimeException('An invoice with an unreversed payment cannot be cancelled. Use a credit or payment reversal workflow.');if(!empty($document['stock_processed_at']))throw new RuntimeException('A fulfilled invoice cannot be cancelled until its stock movement is reversed.');}
                $note=trim((string)($_POST['status_note']??$_POST['reason']??''));db()->prepare('UPDATE documents SET status=?,sent_at=IF(?="sent",COALESCE(sent_at,NOW()),sent_at),accepted_at=IF(?="accepted",COALESCE(accepted_at,NOW()),accepted_at),rejected_at=IF(?="rejected",COALESCE(rejected_at,NOW()),rejected_at),cancelled_at=IF(?="cancelled",COALESCE(cancelled_at,NOW()),cancelled_at),share_enabled=IF(?="cancelled",0,share_enabled),lock_version=lock_version+1 WHERE id=?')->execute([$status,$status,$status,$status,$status,$status,$id]);record_document_event($id,'status_changed',$current,$status,$note!==''?$note:null);audit('status_changed','document',$id,$current.' -> '.$status.($note!==''?': '.$note:''));db()->commit();flash('success','Document status updated.');redirect('index.php?page=document-view&id='.$id);

            case 'fulfil_invoice':
                $invoiceId=(int)($_POST['id']??0);db()->beginTransaction();$s=db()->prepare('SELECT number,status,stock_processed_at FROM documents WHERE id=? AND type="invoice" FOR UPDATE');$s->execute([$invoiceId]);$invoice=$s->fetch();if(!$invoice)throw new RuntimeException('Invoice not found.');if(!in_array($invoice['status'],['sent','overdue','partial','paid'],true))throw new RuntimeException('Only an active issued invoice can be fulfilled. Send the invoice before deducting stock.');if($invoice['stock_processed_at'])throw new RuntimeException('Stock has already been deducted for this invoice.');$items=db()->prepare('SELECT i.product_id,i.quantity,p.name,p.stock_quantity,p.track_stock FROM document_items i JOIN products p ON p.id=i.product_id WHERE i.document_id=? ORDER BY p.id FOR UPDATE');$items->execute([$invoiceId]);$tracked=[];foreach($items as $item){if(!$item['track_stock'])continue;if((float)$item['stock_quantity']<(float)$item['quantity'])throw new RuntimeException('Not enough stock for '.$item['name'].'. Update stock before fulfilment.');$tracked[]=$item;}foreach($tracked as $item){$stockUpdate=db()->prepare('UPDATE products SET stock_quantity=stock_quantity-? WHERE id=? AND stock_quantity>=?');$stockUpdate->execute([$item['quantity'],$item['product_id'],$item['quantity']]);if($stockUpdate->rowCount()!==1)throw new RuntimeException('Stock changed while fulfilling '.$item['name'].'. Review the latest stock level and try again.');db()->prepare('INSERT INTO inventory_movements (product_id,movement_type,quantity_change,reference_type,reference_id,notes,created_by) VALUES (? ,"sale",?,"invoice",?,?,?)')->execute([$item['product_id'],-(float)$item['quantity'],$invoiceId,$invoice['number'],user()['id']]);}db()->prepare('UPDATE documents SET stock_processed_at=NOW(),lock_version=lock_version+1 WHERE id=?')->execute([$invoiceId]);record_document_event($invoiceId,'invoice_fulfilled',$invoice['status'],$invoice['status'],'Tracked stock deducted for '.$invoice['number']);audit('invoice_fulfilled','invoice',$invoiceId,$invoice['number']);db()->commit();flash('success','Order marked fulfilled and stock deducted once.');redirect('index.php?page=document-view&id='.$invoiceId);

            case 'toggle_invoice_share':
                $invoiceId=(int)($_POST['id']??0);$enabled=(int)($_POST['enabled']??0)===1?1:0;$s=db()->prepare('SELECT share_token FROM documents WHERE id=? AND type="invoice"');$s->execute([$invoiceId]);$token=$s->fetchColumn();if($token===false)throw new RuntimeException('Invoice not found.');if(!$token)$token=bin2hex(random_bytes(32));db()->prepare('UPDATE documents SET share_token=?,share_enabled=? WHERE id=?')->execute([$token,$enabled,$invoiceId]);audit($enabled?'invoice_link_enabled':'invoice_link_disabled','invoice',$invoiceId);flash('success',$enabled?'Customer invoice link enabled.':'Customer invoice link disabled.');redirect($_SERVER['HTTP_REFERER']??'index.php');

            case 'open_whatsapp':
                $customerId=(int)($_POST['customer_id']??0);$messageType=(string)($_POST['message_type']??'custom');$customerStmt=db()->prepare('SELECT id,name,company_name,phone,whatsapp,next_refill_due FROM customers WHERE id=? AND active=1');$customerStmt->execute([$customerId]);$customer=$customerStmt->fetch();if(!$customer)throw new RuntimeException('Select a valid active customer.');$digits=preg_replace('/\D+/','',(string)($customer['whatsapp']?:$customer['phone']));if(str_starts_with($digits,'00'))$digits=substr($digits,2);if(str_starts_with($digits,'0'))$digits='263'.substr($digits,1);elseif(strlen($digits)===9&&str_starts_with($digits,'7'))$digits='263'.$digits;if(strlen($digits)<10)throw new RuntimeException('This customer does not have a valid WhatsApp number.');$firstName=trim(explode(' ',trim((string)$customer['name']))[0]??'Customer');$subject='WhatsApp message';$contextId=null;
                if(in_array($messageType,['invoice','overdue'],true)){$contextId=(int)($_POST['document_id']??0);$s=db()->prepare('SELECT d.*,'.finance_document_balance_sql('d').' balance FROM documents d WHERE d.id=? AND d.customer_id=? AND d.type="invoice"');$s->execute([$contextId,$customerId]);$document=$s->fetch();if(!$document)throw new RuntimeException('The selected invoice does not belong to this customer.');$token=$document['share_token']?:bin2hex(random_bytes(32));db()->prepare('UPDATE documents SET share_token=?,share_enabled=1 WHERE id=?')->execute([$token,$contextId]);$balance=max(0,(float)$document['balance']);$shareUrl='https://billing.sodazw.co.zw/share.php?token='.$token;$message=$messageType==='overdue'?'Hello '.$firstName.', a friendly reminder that Soda ZW invoice '.$document['number'].' has an outstanding balance of '.money($balance,$document['currency_code']).'. You can view the invoice here: '.$shareUrl.'. Please let us know when payment has been made. Thank you.':'Hello '.$firstName.', your Soda ZW invoice '.$document['number'].' is ready. Total: '.money($document['total'],$document['currency_code']).'. Balance: '.money($balance,$document['currency_code']).'. View or save it here: '.$shareUrl.'. Thank you for choosing Soda ZW.';$subject=($messageType==='overdue'?'Payment reminder ':'Invoice sent ').$document['number'];}
                elseif($messageType==='refill'){$contextId=(int)($_POST['refill_id']??0);$s=db()->prepare('SELECT * FROM refill_jobs WHERE id=? AND customer_id=?');$s->execute([$contextId,$customerId]);$refill=$s->fetch();if(!$refill)throw new RuntimeException('The selected refill does not belong to this customer.');$total=(float)$refill['quantity']*(float)$refill['unit_price']+(float)$refill['delivery_fee'];$message='Hello '.$firstName.', your Soda ZW refill '.$refill['reference'].' is currently '.str_replace('_',' ',$refill['status']).'. Total: '.money($total).'.'.($refill['due_at']?' Expected completion: '.date('j M Y, H:i',strtotime($refill['due_at'])).'.':'').' We will keep you updated. Thank you.';$subject='Refill update '.$refill['reference'];}
                elseif($messageType==='delivery'){$contextId=(int)($_POST['delivery_id']??0);$s=db()->prepare('SELECT * FROM deliveries WHERE id=? AND customer_id=?');$s->execute([$contextId,$customerId]);$delivery=$s->fetch();if(!$delivery)throw new RuntimeException('The selected delivery does not belong to this customer.');$message='Hello '.$firstName.', your Soda ZW delivery '.$delivery['reference'].' is '.str_replace('_',' ',$delivery['status']).'.'.($delivery['scheduled_for']?' Scheduled for '.date('j M Y, H:i',strtotime($delivery['scheduled_for'])).'.':'').' Delivery fee: '.money($delivery['delivery_fee']).'. Delivery address: '.$delivery['delivery_address'].'.';$subject='Delivery update '.$delivery['reference'];}
                elseif($messageType==='refill_reminder'){$due=$customer['next_refill_due']?date('j M Y',strtotime($customer['next_refill_due'])):'now';$message='Hello '.$firstName.', your Soda ZW cylinder refill may be due '.$due.'. Refills are $35 per approved compatible cylinder and are completed overnight. Delivery can be arranged at an additional location-based fee. Reply here to book your refill.';$subject='Customer refill reminder';}
                else{$message=trim((string)($_POST['message']??''));if($message==='')throw new RuntimeException('Enter a WhatsApp message.');$message=substr($message,0,1800);$subject='Custom WhatsApp message';}
                db()->prepare('INSERT INTO crm_activities (customer_id,activity_type,subject,completed_at,notes,assigned_to,created_by) VALUES (?,"whatsapp",?,NOW(),?,?,?)')->execute([$customerId,$subject,$message,user()['id'],user()['id']]);db()->prepare('UPDATE customers SET last_contact_at=NOW() WHERE id=?')->execute([$customerId]);audit('whatsapp_opened','customer',$customerId,$subject.($contextId?' #'.$contextId:''));redirect('https://wa.me/'.$digits.'?text='.rawurlencode($message));

            case 'send_whatsapp_api':
                $conversationId=(int)($_POST['conversation_id']??0);$mode=(string)($_POST['send_mode']??'text');
                if($mode==='template'){$template=trim((string)($_POST['template_name']??''));$language=trim((string)($_POST['template_language']??'en_US'));$raw=trim((string)($_POST['template_parameters']??''));$parameters=$raw===''?[]:array_values(array_filter(array_map('trim',preg_split('/\r?\n/',$raw)?:[]),fn($v)=>$v!==''));whatsapp_send($conversationId,'',user()['id'],$template,$language,$parameters);flash('success','Approved WhatsApp template sent through Meta.');}
                else{$body=trim((string)($_POST['message']??''));whatsapp_send($conversationId,$body,user()['id']);flash('success','WhatsApp reply sent from the shared inbox.');}
                audit('whatsapp_api_sent','whatsapp_conversation',$conversationId,$mode);redirect('index.php?page=whatsapp&conversation='.$conversationId);

            case 'update_whatsapp_conversation':
                $conversationId=(int)($_POST['conversation_id']??0);$status=in_array($_POST['status']??'', ['open','pending','closed'],true)?$_POST['status']:'open';$assigned=(int)($_POST['assigned_to']??0)?:null;
                if($assigned){$check=db()->prepare('SELECT id FROM users WHERE id=? AND active=1');$check->execute([$assigned]);if(!$check->fetchColumn())throw new RuntimeException('Select an active team member.');}
                $stmt=db()->prepare('UPDATE whatsapp_conversations SET status=?,assigned_to=? WHERE id=?');$stmt->execute([$status,$assigned,$conversationId]);if(!$stmt->rowCount())throw new RuntimeException('WhatsApp conversation not found.');audit('whatsapp_conversation_updated','whatsapp_conversation',$conversationId,$status);flash('success','Conversation assignment updated.');redirect('index.php?page=whatsapp&conversation='.$conversationId);

            case 'link_whatsapp_customer':
                $conversationId=(int)($_POST['conversation_id']??0);$customerId=(int)($_POST['customer_id']??0);$check=db()->prepare('SELECT id FROM customers WHERE id=? AND active=1');$check->execute([$customerId]);if(!$check->fetchColumn())throw new RuntimeException('Select an active customer.');
                db()->prepare('UPDATE whatsapp_conversations SET customer_id=? WHERE id=?')->execute([$customerId,$conversationId]);db()->prepare('UPDATE whatsapp_messages SET customer_id=? WHERE conversation_id=?')->execute([$customerId,$conversationId]);audit('whatsapp_customer_linked','whatsapp_conversation',$conversationId,'Customer '.$customerId);flash('success','Conversation linked to the customer record.');redirect('index.php?page=whatsapp&conversation='.$conversationId);

            case 'create_customer_from_whatsapp':
                $conversationId=(int)($_POST['conversation_id']??0);$name=trim((string)($_POST['name']??''));if($name==='')throw new RuntimeException('Enter the customer name.');$stmt=db()->prepare('SELECT phone,display_name,customer_id FROM whatsapp_conversations WHERE id=?');$stmt->execute([$conversationId]);$conversation=$stmt->fetch();if(!$conversation||$conversation['customer_id'])throw new RuntimeException('This conversation is unavailable or already linked.');
                db()->beginTransaction();db()->prepare('INSERT INTO customers (name,phone,whatsapp,customer_type,marketing_consent,notes) VALUES (?,?,?,"Household",0,"Created from the Level 2 WhatsApp inbox")')->execute([$name,'+'.$conversation['phone'],'+'.$conversation['phone']]);$customerId=(int)db()->lastInsertId();db()->prepare('UPDATE whatsapp_conversations SET customer_id=? WHERE id=?')->execute([$customerId,$conversationId]);db()->prepare('UPDATE whatsapp_messages SET customer_id=? WHERE conversation_id=?')->execute([$customerId,$conversationId]);db()->commit();audit('customer_created_from_whatsapp','customer',$customerId);flash('success','Customer created and linked to the WhatsApp conversation.');redirect('index.php?page=whatsapp&conversation='.$conversationId);

            case 'start_whatsapp_conversation':
                $customerId=(int)($_POST['customer_id']??0);$stmt=db()->prepare('SELECT id,name,company_name,phone,whatsapp FROM customers WHERE id=? AND active=1');$stmt->execute([$customerId]);$customer=$stmt->fetch();if(!$customer)throw new RuntimeException('Select an active customer.');$conversation=whatsapp_conversation((string)($customer['whatsapp']?:$customer['phone']),(string)($customer['company_name']?:$customer['name']));db()->prepare('UPDATE whatsapp_conversations SET customer_id=? WHERE id=?')->execute([$customerId,$conversation['id']]);redirect('index.php?page=whatsapp&conversation='.$conversation['id']);

            case 'convert_quote':
                $quoteId=(int)($_POST['id']??0);if(!$quoteId)throw new RuntimeException('Quotation not found.');db()->beginTransaction();$q=db()->prepare('SELECT * FROM documents WHERE id=? AND type="quotation" FOR UPDATE');$q->execute([$quoteId]);$quote=$q->fetch();if(!$quote)throw new RuntimeException('Quotation not found.');
                $conversionStmt=db()->prepare('SELECT invoice_id FROM quotation_conversions WHERE quotation_id=? LIMIT 1');$conversionStmt->execute([$quoteId]);if($existing=$conversionStmt->fetchColumn()){db()->commit();redirect('index.php?page=document-view&id='.$existing);}
                $legacyStmt=db()->prepare('SELECT id FROM documents WHERE source_document_id=? AND type="invoice" ORDER BY id LIMIT 1 FOR UPDATE');$legacyStmt->execute([$quoteId]);if($existing=$legacyStmt->fetchColumn()){db()->prepare('INSERT IGNORE INTO quotation_conversions (quotation_id,invoice_id,created_by) VALUES (?,?,?)')->execute([$quoteId,$existing,user()['id']]);db()->commit();redirect('index.php?page=document-view&id='.$existing);}
                if($quote['status']!=='accepted')throw new RuntimeException('Mark the quotation as accepted after the customer confirms it, then convert it to an invoice.');$number=reserve_unique_number('invoice');$due=date('Y-m-d',strtotime('+'.max(0,(int)(settings()['default_due_days']??7)).' days'));
                $stmt=db()->prepare('INSERT INTO documents (type,number,customer_id,source_document_id,issue_date,due_date,status,currency_code,subtotal,discount_type,discount_value,discount_total,tax_total,delivery_fee,total,notes,terms,order_source,fulfilment_method,delivery_address,share_token,customer_reference,created_by) VALUES ("invoice",?,?,?,?,?,"draft",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
                $stmt->execute([$number,$quote['customer_id'],$quoteId,date('Y-m-d'),$due,$quote['currency_code'],$quote['subtotal'],$quote['discount_type'],$quote['discount_value'],$quote['discount_total'],$quote['tax_total'],$quote['delivery_fee'],$quote['total'],$quote['notes'],$quote['terms'],$quote['order_source'],$quote['fulfilment_method'],$quote['delivery_address'],bin2hex(random_bytes(32)),$quote['customer_reference']??'',user()['id']]);$invoiceId=(int)db()->lastInsertId();
                db()->prepare('INSERT INTO document_items (document_id,product_id,description,quantity,unit_price,unit_cost,tax_rate,line_subtotal,line_tax,line_total,sort_order) SELECT ?,product_id,description,quantity,unit_price,unit_cost,tax_rate,line_subtotal,line_tax,line_total,sort_order FROM document_items WHERE document_id=?')->execute([$invoiceId,$quoteId]);db()->prepare('INSERT INTO quotation_conversions (quotation_id,invoice_id,created_by) VALUES (?,?,?)')->execute([$quoteId,$invoiceId,user()['id']]);db()->prepare('UPDATE documents SET accepted_at=COALESCE(accepted_at,NOW()),lock_version=lock_version+1 WHERE id=?')->execute([$quoteId]);record_document_event($quoteId,'quotation_converted','accepted','accepted','Converted to draft invoice '.$number);record_document_event($invoiceId,'created_from_quotation',null,'draft','Created from quotation '.$quote['number']);audit('quotation_converted','invoice',$invoiceId,$number);db()->commit();flash('success','Quotation converted to draft invoice '.$number.'. Review it before sending.');redirect('index.php?page=document-view&id='.$invoiceId);

            case 'create_invoice_paynow_link':
                $invoiceId=(int)($_POST['invoice_id']??0);if(!$invoiceId)throw new RuntimeException('Invoice not found.');
                $checkout=paynow_checkout_for_invoice($invoiceId);
                audit('paynow_link_created','invoice',$invoiceId,(string)$checkout['reference']);
                flash('success','Secure Paynow payment link is ready to copy or send.');
                redirect('index.php?page=document-view&id='.$invoiceId.'#paynow-payment');

            case 'refresh_invoice_paynow_status':
                $invoiceId=(int)($_POST['invoice_id']??0);if(!$invoiceId)throw new RuntimeException('Invoice not found.');
                $find=db()->prepare('SELECT poll_url,merchant_reference FROM online_payment_attempts WHERE provider="paynow" AND invoice_id=? ORDER BY id DESC LIMIT 1');$find->execute([$invoiceId]);$attempt=$find->fetch();
                if(!$attempt||empty($attempt['poll_url']))throw new RuntimeException('Create the Paynow link before checking its status.');
                $result=paynow_apply_status(paynow_poll((string)$attempt['poll_url']));
                audit('paynow_status_checked','invoice',$invoiceId,(string)$result['status']);
                flash($result['paid']?'success':'info',$result['paid']?'Paynow payment confirmed and recorded.':'Paynow status: '.str_replace('_',' ',(string)$result['status']).'.');
                redirect('index.php?page=document-view&id='.$invoiceId.'#paynow-payment');

            case 'record_payment':
                $invoiceId=(int)($_POST['invoice_id']??0);$accountId=(int)($_POST['account_id']??0);$paymentDate=(string)($_POST['payment_date']??'');$dateValue=DateTime::createFromFormat('!Y-m-d',$paymentDate);if(!$invoiceId||!$accountId||!$dateValue||$dateValue->format('Y-m-d')!==$paymentDate)throw new RuntimeException('Select an invoice, payment account and valid payment date.');db()->beginTransaction();$invoiceStmt=db()->prepare('SELECT id,currency_code,status,total FROM documents WHERE id=? AND type="invoice" FOR UPDATE');$invoiceStmt->execute([$invoiceId]);$invoice=$invoiceStmt->fetch();if(!$invoice)throw new RuntimeException('Invoice not found.');if(in_array($invoice['status'],['draft','cancelled'],true))throw new RuntimeException('Send the invoice before receiving a payment against it.');$accountStmt=db()->prepare('SELECT * FROM payment_accounts WHERE id=? AND active=1');$accountStmt->execute([$accountId]);$account=$accountStmt->fetch();if(!$account)throw new RuntimeException('Select a valid payment account.');$invoiceCurrency=(string)$invoice['currency_code'];
                $received=max(0,decimal($_POST['received_amount']??0));$rate=max(0,(float)($_POST['exchange_rate']??1));if($account['currency_code']===$invoiceCurrency)$rate=1;if($received<=0||$rate<=0)throw new RuntimeException('Enter the amount received and a valid exchange rate.');$amount=round($received/$rate,2);$fee=max(0,decimal($_POST['fee_amount']??0));$financials=invoice_financials($invoiceId);$balance=round((float)$financials['balance'],2);if($balance<=.009)throw new RuntimeException('This invoice is already settled in full.');if($amount<=0||$amount>$balance+.009)throw new RuntimeException('The applied payment cannot exceed the outstanding balance after credits and reversals.');
                $reference=trim((string)($_POST['reference']??''));db()->prepare('INSERT INTO payments (invoice_id,account_id,payment_date,amount,received_amount,received_currency,exchange_rate,fee_amount,method,reference,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')->execute([$invoiceId,$accountId,$paymentDate,$amount,$received,$account['currency_code'],$rate,$fee,$account['name'],$reference,trim((string)($_POST['notes']??'')),user()['id']]);$id=(int)db()->lastInsertId();$previousStatus=(string)$invoice['status'];update_invoice_status($invoiceId);$statusStmt=db()->prepare('SELECT status FROM documents WHERE id=?');$statusStmt->execute([$invoiceId]);$newStatus=(string)$statusStmt->fetchColumn();db()->prepare('UPDATE documents SET lock_version=lock_version+1 WHERE id=?')->execute([$invoiceId]);record_document_event($invoiceId,'payment_recorded',$previousStatus,$newStatus,'Payment #'.$id.' applied '.number_format($amount,2).' '.$invoiceCurrency.($reference!==''?' ('.$reference.')':''));audit('payment_recorded','payment',$id,$account['name'].' '.$received.' '.$account['currency_code']);db()->commit();flash('success','Payment recorded through '.$account['name'].'.');redirect('index.php?page=document-view&id='.$invoiceId);

            case 'save_expense':
                $amount=decimal($_POST['amount']??0);$currency=strtoupper(trim((string)($_POST['currency_code']??(settings()['currency_code']??'USD'))));if($amount<=0||!preg_match('/^[A-Z]{3}$/',$currency))throw new RuntimeException('Enter a valid expense amount and currency.');db()->prepare('INSERT INTO expenses (expense_date,category,supplier,description,amount,currency_code,reference,created_by) VALUES (?,?,?,?,?,?,?,?)')->execute([$_POST['expense_date'],trim($_POST['category']),trim($_POST['supplier']??''),trim($_POST['description']),$amount,$currency,trim($_POST['reference']??''),user()['id']]);audit('expense_created','expense',(int)db()->lastInsertId());flash('success','Expense recorded.');redirect('index.php?page=expenses');

            case 'save_supplier':
                $id=(int)($_POST['id']??0);$name=trim($_POST['name']??'');if($name==='')throw new RuntimeException('Supplier name is required.');$values=[$name,trim($_POST['contact_name']??''),trim($_POST['email']??''),trim($_POST['phone']??''),trim($_POST['tax_number']??''),trim($_POST['address']??''),trim($_POST['notes']??'')];if($id){$values[]=$id;db()->prepare('UPDATE suppliers SET name=?,contact_name=?,email=?,phone=?,tax_number=?,address=?,notes=? WHERE id=?')->execute($values);}else{db()->prepare('INSERT INTO suppliers (name,contact_name,email,phone,tax_number,address,notes) VALUES (?,?,?,?,?,?,?)')->execute($values);$id=(int)db()->lastInsertId();}audit('supplier_saved','supplier',$id);flash('success','Supplier saved.');redirect('index.php?page=suppliers');

            case 'save_bill':
                $supplierId=(int)($_POST['supplier_id']??0);$number=trim($_POST['number']??'');$total=decimal($_POST['total']??0);$currency=strtoupper(trim($_POST['currency_code']??'USD'));if(!$supplierId||$number===''||$total<=0||!preg_match('/^[A-Z]{3}$/',$currency))throw new RuntimeException('Enter the supplier, bill number, currency and total.');db()->prepare('INSERT INTO bills (supplier_id,number,bill_date,due_date,description,currency_code,total,reference,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?)')->execute([$supplierId,$number,$_POST['bill_date'],$_POST['due_date'],trim($_POST['description']??''),$currency,$total,trim($_POST['reference']??''),trim($_POST['notes']??''),user()['id']]);$id=(int)db()->lastInsertId();audit('bill_created','bill',$id,$number);flash('success','Supplier bill recorded.');redirect('index.php?page=bills');

            case 'record_bill_payment':
                $billId=(int)($_POST['bill_id']??0);$accountId=(int)($_POST['account_id']??0);$amount=decimal($_POST['amount']??0);$s=db()->prepare('SELECT b.total,b.currency_code,COALESCE(SUM(bp.amount),0) paid FROM bills b LEFT JOIN bill_payments bp ON bp.bill_id=b.id WHERE b.id=? AND b.status<>"cancelled" GROUP BY b.id');$s->execute([$billId]);$bill=$s->fetch();if(!$bill)throw new RuntimeException('Bill not found.');$a=db()->prepare('SELECT currency_code FROM payment_accounts WHERE id=? AND active=1');$a->execute([$accountId]);$accountCurrency=$a->fetchColumn();if(!$accountCurrency||$accountCurrency!==$bill['currency_code'])throw new RuntimeException('Select a payment account in the same currency as the bill.');$balance=max(0,(float)$bill['total']-(float)$bill['paid']);if($amount<=0||$amount>$balance+.01)throw new RuntimeException('The payment cannot exceed the outstanding bill balance.');db()->prepare('INSERT INTO bill_payments (bill_id,account_id,payment_date,amount,reference,notes,created_by) VALUES (?,?,?,?,?,?,?)')->execute([$billId,$accountId,$_POST['payment_date'],$amount,trim($_POST['reference']??''),trim($_POST['notes']??''),user()['id']]);$newPaid=(float)$bill['paid']+$amount;$status=$newPaid>=(float)$bill['total']-.01?'paid':'partial';db()->prepare('UPDATE bills SET status=? WHERE id=?')->execute([$status,$billId]);audit('bill_payment_recorded','bill_payment',(int)db()->lastInsertId());flash('success','Bill payment recorded.');redirect('index.php?page=bills');

            case 'save_settings':
                require_role(['owner','admin']);$stmt=db()->prepare('UPDATE settings SET company_name=?,trading_name=?,email=?,phone=?,address=?,city=?,country=?,tax_number=?,currency_code=?,invoice_prefix=?,quote_prefix=?,default_tax_rate=?,default_due_days=?,bank_details=?,payment_instructions=?,invoice_terms=? WHERE id=1');
                $stmt->execute([trim($_POST['company_name']),trim($_POST['trading_name']),trim($_POST['email']),trim($_POST['phone']),trim($_POST['address']),trim($_POST['city']),trim($_POST['country']),trim($_POST['tax_number']),strtoupper(trim($_POST['currency_code'])),trim($_POST['invoice_prefix']),trim($_POST['quote_prefix']),max(0,decimal($_POST['default_tax_rate'])),max(0,(int)$_POST['default_due_days']),trim($_POST['bank_details']),trim($_POST['payment_instructions']),trim($_POST['invoice_terms'])]);settings(true);audit('settings_updated','settings',1);flash('success','Business settings updated.');redirect('index.php?page=settings');

            case 'save_payment_account':
                require_role(['owner','admin']);$name=trim($_POST['name']??'');$currency=strtoupper(trim($_POST['currency_code']??'USD'));if($name===''||!preg_match('/^[A-Z]{3}$/',$currency))throw new RuntimeException('Enter an account name and a three-letter currency code.');$type=in_array($_POST['type']??'', ['cash','mobile_money','bank','card','online','other'],true)?$_POST['type']:'other';db()->prepare('INSERT INTO payment_accounts (name,type,currency_code,details) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE type=VALUES(type),details=VALUES(details),active=1')->execute([$name,$type,$currency,trim($_POST['details']??'')]);audit('payment_account_saved','payment_account',(int)db()->lastInsertId(),$name);flash('success','Payment account saved.');redirect('index.php?page=settings');

            case 'change_own_password':
                require_auth();$current=(string)($_POST['current_password']??'');$password=(string)($_POST['new_password']??'');$confirm=(string)($_POST['confirm_password']??'');if(strlen($password)<12)throw new RuntimeException('Your new password must contain at least 12 characters.');if($password!==$confirm)throw new RuntimeException('The new password confirmation does not match.');$stmt=db()->prepare('SELECT password_hash FROM users WHERE id=? AND active=1');$stmt->execute([user()['id']]);$hash=$stmt->fetchColumn();if(!$hash||!password_verify($current,$hash))throw new RuntimeException('Your current password is incorrect.');if(password_verify($password,$hash))throw new RuntimeException('Choose a new password that is different from your current password.');$userId=(int)user()['id'];db()->prepare('UPDATE users SET password_hash=?,session_version=session_version+1 WHERE id=?')->execute([password_hash($password,PASSWORD_DEFAULT),$userId]);$_SESSION['user']['session_version']=(int)($_SESSION['user']['session_version']??1)+1;audit('own_password_changed','user',$userId);session_regenerate_id(true);flash('success','Your password has been changed securely. Other signed-in sessions were revoked.');redirect('index.php?page=profile');

            case 'update_user':
                require_role(['owner']);$id=(int)($_POST['id']??0);$name=trim($_POST['name']??'');$email=strtolower(trim($_POST['email']??''));$role=in_array($_POST['role']??'', ['owner','admin','staff'],true)?$_POST['role']:'staff';$active=isset($_POST['active'])?1:0;if(!$id||$name===''||!filter_var($email,FILTER_VALIDATE_EMAIL))throw new RuntimeException('Enter a valid user name and email address.');$stmt=db()->prepare('SELECT id,role,active FROM users WHERE id=?');$stmt->execute([$id]);$target=$stmt->fetch();if(!$target)throw new RuntimeException('User account not found.');if($id===(int)user()['id']&&($role!=='owner'||!$active))throw new RuntimeException('You cannot remove your own owner access or deactivate your own account.');if($target['role']==='owner'&&($role!=='owner'||!$active)){$count=(int)db()->query('SELECT COUNT(*) FROM users WHERE role="owner" AND active=1')->fetchColumn();if($count<=1)throw new RuntimeException('The last active owner cannot be demoted or deactivated.');}db()->prepare('UPDATE users SET name=?,email=?,role=?,active=?,session_version=session_version+1 WHERE id=?')->execute([$name,$email,$role,$active,$id]);if($id===(int)user()['id']){$_SESSION['user']['name']=$name;$_SESSION['user']['email']=$email;$_SESSION['user']['role']=$role;$_SESSION['user']['session_version']=(int)($_SESSION['user']['session_version']??1)+1;}audit('user_updated','user',$id,$role.($active?' active':' inactive'));flash('success','User access updated and previous sessions revoked.');redirect('index.php?page=team');

            case 'reset_user_password':
                require_role(['owner']);$id=(int)($_POST['id']??0);$password=(string)($_POST['new_password']??'');$confirm=(string)($_POST['confirm_password']??'');if($id===(int)user()['id'])throw new RuntimeException('Use My profile to change your own password securely.');if(!$id||strlen($password)<12)throw new RuntimeException('The new password must contain at least 12 characters.');if($password!==$confirm)throw new RuntimeException('The password confirmation does not match.');$stmt=db()->prepare('SELECT id FROM users WHERE id=?');$stmt->execute([$id]);if(!$stmt->fetchColumn())throw new RuntimeException('User account not found.');db()->prepare('UPDATE users SET password_hash=?,session_version=session_version+1 WHERE id=?')->execute([password_hash($password,PASSWORD_DEFAULT),$id]);audit('user_password_reset','user',$id);flash('success','The user password has been reset and all previous sessions were revoked.');redirect('index.php?page=team');

            case 'add_user':
                require_role(['owner']);$name=trim($_POST['name']??'');$email=strtolower(trim($_POST['email']??''));$password=(string)($_POST['password']??'');$confirm=(string)($_POST['confirm_password']??'');if($name===''||!filter_var($email,FILTER_VALIDATE_EMAIL)||strlen($password)<12)throw new RuntimeException('Use a name, valid email and password of at least 12 characters.');if($password!==$confirm)throw new RuntimeException('The password confirmation does not match.');$role=in_array($_POST['role']??'', ['owner','admin','staff'],true)?$_POST['role']:'staff';db()->prepare('INSERT INTO users (name,email,password_hash,role) VALUES (?,?,?,?)')->execute([$name,$email,password_hash($password,PASSWORD_DEFAULT),$role]);audit('user_created','user',(int)db()->lastInsertId(),$role);flash('success','Team member added. They can now sign in with their email and temporary password.');redirect('index.php?page=team');
        }
    } catch (Throwable $e) {
        if (db()->inTransaction()) db()->rollBack(); flash('error',$e instanceof PDOException?'The information could not be saved. Check for duplicate references and try again.':$e->getMessage()); redirect($_SERVER['HTTP_REFERER']??'index.php');
    }
}
