<?php
declare(strict_types=1);

function invoice_payment_total(int $invoiceId): float {
    $stmt=db()->prepare('SELECT COALESCE(SUM(p.amount),0)-COALESCE((SELECT SUM(r.amount) FROM payment_reversals r JOIN payments px ON px.id=r.payment_id WHERE px.invoice_id=?),0) FROM payments p WHERE p.invoice_id=?');
    $stmt->execute([$invoiceId,$invoiceId]);
    return max(0,(float)$stmt->fetchColumn());
}

function invoice_credit_total(int $invoiceId): float {
    $stmt=db()->prepare('SELECT COALESCE(SUM(amount),0) FROM credit_note_applications WHERE invoice_id=?');
    $stmt->execute([$invoiceId]);
    return max(0,(float)$stmt->fetchColumn());
}

function invoice_financials(int $invoiceId): array {
    $stmt=db()->prepare('SELECT total,currency_code,status FROM documents WHERE id=? AND type="invoice"');
    $stmt->execute([$invoiceId]);
    $invoice=$stmt->fetch();
    if(!$invoice)return ['total'=>0.0,'paid'=>0.0,'credited'=>0.0,'balance'=>0.0,'currency_code'=>'USD','status'=>'missing'];
    $paid=invoice_payment_total($invoiceId);$credited=invoice_credit_total($invoiceId);
    return ['total'=>(float)$invoice['total'],'paid'=>$paid,'credited'=>$credited,'balance'=>max(0,(float)$invoice['total']-$paid-$credited),'currency_code'=>$invoice['currency_code'],'status'=>$invoice['status']];
}

function permission_defaults(string $role): array {
    $keys=['customers.manage','crm.manage','documents.create','documents.manage','payments.record','website_orders.manage','whatsapp.manage','operations.manage','catalogue.manage','stock.adjust','purchasing.manage','expenses.manage','credits.manage','reconciliation.manage','reminders.manage','stock.receive','reports.view','settings.manage','backups.manage','users.manage','imports.manage'];
    if($role==='owner')return array_fill_keys($keys,true);
    if($role==='admin'){
        $permissions=array_fill_keys($keys,true);
        $permissions['users.manage']=false;
        return $permissions;
    }
    return [
        'customers.manage'=>true,'crm.manage'=>true,'documents.create'=>true,'documents.manage'=>true,
        'payments.record'=>true,'website_orders.manage'=>true,'whatsapp.manage'=>true,'operations.manage'=>true,
        'catalogue.manage'=>false,'stock.adjust'=>false,'purchasing.manage'=>false,'expenses.manage'=>false,
        'credits.manage'=>false,'reconciliation.manage'=>false,'reminders.manage'=>true,'stock.receive'=>false,
        'reports.view'=>true,'settings.manage'=>false,'backups.manage'=>false,'users.manage'=>false,'imports.manage'=>false
    ];
}

function has_permission(string $key,?array $account=null): bool {
    $account=$account??user();if(!$account)return false;if(($account['role']??'')==='owner')return true;
    try{$stmt=db()->prepare('SELECT allowed FROM user_permissions WHERE user_id=? AND permission_key=?');$stmt->execute([(int)$account['id'],$key]);$override=$stmt->fetchColumn();if($override!==false)return (bool)$override;}catch(Throwable $ignored){}
    return permission_defaults((string)($account['role']??'staff'))[$key]??false;
}

function require_permission(string $key): void {
    require_auth();if(!has_permission($key)){http_response_code(403);exit('You do not have permission to use this finance control.');}
}

function finance_document_balance_sql(string $alias='d'): string {
    return 'GREATEST('.$alias.'.total-COALESCE((SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id='.$alias.'.id),0)+COALESCE((SELECT SUM(r.amount) FROM payment_reversals r JOIN payments rp ON rp.id=r.payment_id WHERE rp.invoice_id='.$alias.'.id),0)-COALESCE((SELECT SUM(ca.amount) FROM credit_note_applications ca WHERE ca.invoice_id='.$alias.'.id),0),0)';
}

function management_report_summary(string $currency,string $from,string $to): array {
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)||!preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)||$to<$from){
        throw new RuntimeException('Choose a valid reporting period.');
    }
    $value=static function(string $sql,array $params): float {
        $stmt=db()->prepare($sql);$stmt->execute($params);return (float)$stmt->fetchColumn();
    };
    $period=[$currency,$from,$to];
    $gross=$value('SELECT COALESCE(SUM(total),0) FROM documents WHERE type="invoice" AND status NOT IN ("draft","cancelled") AND currency_code=? AND issue_date BETWEEN ? AND ?',$period);
    $credits=$value('SELECT COALESCE(SUM(total),0) FROM documents WHERE type="credit_note" AND status NOT IN ("draft","cancelled") AND currency_code=? AND issue_date BETWEEN ? AND ?',$period);
    $grossTax=$value('SELECT COALESCE(SUM(tax_total),0) FROM documents WHERE type="invoice" AND status NOT IN ("draft","cancelled") AND currency_code=? AND issue_date BETWEEN ? AND ?',$period);
    $creditTax=$value('SELECT COALESCE(SUM(tax_total),0) FROM documents WHERE type="credit_note" AND status NOT IN ("draft","cancelled") AND currency_code=? AND issue_date BETWEEN ? AND ?',$period);
    $tax=$grossTax-$creditTax;
    $received=$value('SELECT COALESCE(SUM(p.amount),0) FROM payments p JOIN documents d ON d.id=p.invoice_id WHERE d.type="invoice" AND d.status<>"cancelled" AND d.currency_code=? AND p.payment_date BETWEEN ? AND ?',$period);
    $refunds=$value('SELECT COALESCE(SUM(r.amount),0) FROM payment_reversals r JOIN payments p ON p.id=r.payment_id JOIN documents d ON d.id=p.invoice_id WHERE d.type="invoice" AND d.status<>"cancelled" AND d.currency_code=? AND r.reversal_date BETWEEN ? AND ?',$period);
    $expenses=$value('SELECT COALESCE(SUM(amount),0) FROM expenses WHERE currency_code=? AND expense_date BETWEEN ? AND ?',$period);
    $grossMargin=$value('SELECT COALESCE(SUM(di.line_subtotal-(di.quantity*di.unit_cost)),0) FROM document_items di JOIN documents d ON d.id=di.document_id WHERE d.type="invoice" AND d.status NOT IN ("draft","cancelled") AND d.currency_code=? AND d.issue_date BETWEEN ? AND ?',$period);
    $stockValue=$value('SELECT COALESCE(SUM(stock_quantity*cost_price),0) FROM products WHERE active=1 AND track_stock=1',[]);
    $netSales=$gross-$credits;
    return [
        'currency'=>$currency,'from'=>$from,'to'=>$to,'gross_sales'=>$gross,'credits'=>$credits,
        'net_sales'=>$netSales,'tax_invoiced'=>$tax,'gross_tax'=>$grossTax,'credit_tax'=>$creditTax,'net_sales_ex_tax'=>$netSales-$tax,
        'cash_received'=>$received,'refunds'=>$refunds,'net_cash'=>$received-$refunds,
        'expenses'=>$expenses,'gross_product_margin_before_credits'=>$grossMargin,
        'stock_value'=>$stockValue,'operating_contribution'=>($netSales-$tax)-$expenses,
    ];
}

function sql_backup_contents(): string {
    $output="-- Soda ZW Accounting OS backup\n-- Generated ".date(DATE_ATOM)."\nSET FOREIGN_KEY_CHECKS=0;\n\n";
    $tables=db()->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    foreach($tables as $table){$safe=str_replace('`','``',(string)$table);$create=db()->query('SHOW CREATE TABLE `'.$safe.'`')->fetch(PDO::FETCH_NUM);$output.="DROP TABLE IF EXISTS `{$safe}`;\n{$create[1]};\n";$rows=db()->query('SELECT * FROM `'.$safe.'`');while($row=$rows->fetch(PDO::FETCH_ASSOC)){$columns=array_map(fn($c)=>'`'.str_replace('`','``',(string)$c).'`',array_keys($row));$values=array_map(fn($v)=>$v===null?'NULL':db()->quote((string)$v),array_values($row));$output.='INSERT INTO `'.$safe.'` ('.implode(',',$columns).') VALUES ('.implode(',',$values).");\n";}$output.="\n";}
    return $output."SET FOREIGN_KEY_CHECKS=1;\n";
}

function queue_invoice_reminder(int $invoiceId,string $channel='manual',?int $userId=null): int {
    $balanceSql=finance_document_balance_sql('d');
    $stmt=db()->prepare('SELECT d.id,d.number,d.customer_id,d.currency_code,d.due_date,c.name,c.email,c.phone,c.whatsapp,'.$balanceSql.' balance FROM documents d JOIN customers c ON c.id=d.customer_id WHERE d.id=? AND d.type="invoice" AND d.status NOT IN ("draft","cancelled")');
    $stmt->execute([$invoiceId]);$invoice=$stmt->fetch();if(!$invoice||$invoice['balance']<=.009)throw new RuntimeException('This invoice has no outstanding balance.');
    $recipient=$channel==='email'?$invoice['email']:($invoice['whatsapp']?:$invoice['phone']);if(!$recipient)throw new RuntimeException('The customer does not have the selected contact detail.');
    $message='Hello '.$invoice['name'].', a friendly Soda ZW reminder that invoice '.$invoice['number'].' has an outstanding balance of '.money($invoice['balance'],$invoice['currency_code']).'. Please use the invoice number as your payment reference. Thank you.';
    $insert=db()->prepare('INSERT INTO reminder_log (document_id,customer_id,channel,recipient,message,status,scheduled_for,created_by) VALUES (?,?,?,?,?,"queued",NOW(),?)');
    $insert->execute([$invoiceId,$invoice['customer_id'],$channel,$recipient,$message,$userId]);return (int)db()->lastInsertId();
}
