<?php
require_once '../app/Models/ReportFlag.php';
class ReportController {
    private $pdo; private $flagsModel;
    public function __construct(){ $this->auth(); $this->pdo=$GLOBALS['pdo']; $this->flagsModel=new ReportFlag(); }
    private function auth(){ if(!isset($_SESSION['user_id'])){ header('Location:/samanta_crm/login'); exit; } }
    public function daily(){
        $date = $_GET['date'] ?? date('Y-m-d');
        // normalize date
        if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)) $date = date('Y-m-d');
        $export = $_GET['export'] ?? null; // csv | pdf
        $flags = $this->flagsModel->allWithInactive(); // include inactive to show maybe differentiate
        $activeFlags = array_filter($flags, fn($f)=> (int)$f['is_active']===1);
        $reports = [];
        foreach($activeFlags as $flag){
            $code = $flag['code']; $viewType = $flag['view_type'] ?? 'category_summary';
            // Load overrides for this flag
            $overridesStmt = $this->pdo->prepare("SELECT product_id,mode FROM report_flag_override_products WHERE report_flag_id=?");
            $overridesStmt->execute([$flag['id']]);
            $includeIds = []; $excludeIds=[];
            while($ov=$overridesStmt->fetch(PDO::FETCH_ASSOC)){ if($ov['mode']==='include') $includeIds[]=$ov['product_id']; else $excludeIds[]=$ov['product_id']; }
            if($viewType==='category_summary'){
                $sqlBase = "SELECT p.id,p.name,p.code,p.production_unit,c.name AS category_name, SUM(oi.quantity) qty
                    FROM products p
                    JOIN order_items oi ON oi.product_id=p.id
                    JOIN orders o ON o.id=oi.order_id AND DATE(o.order_date)=? 
                    LEFT JOIN categories c ON c.id=p.category_id
                    WHERE p.report_flags LIKE ?
                    ";
                if($includeIds){ $inPlace = implode(',',array_fill(0,count($includeIds),'?')); $sqlBase .= " AND p.id IN ($inPlace)"; }
                if($excludeIds){ $exPlace = implode(',',array_fill(0,count($excludeIds),'?')); $sqlBase .= " AND p.id NOT IN ($exPlace)"; }
                $sqlBase .= " GROUP BY p.id HAVING qty > 0 ORDER BY c.name, p.name";
                $stmt = $this->pdo->prepare($sqlBase);
                $like = '%"'.$code.'"%';
                $params = [$date,$like];
                $params = array_merge($params,$includeIds,$excludeIds);
                $stmt->execute($params);
                $rows = $stmt->fetchAll();
                if(!empty($rows)){
                    $reports[] = [ 'flag'=>$flag, 'view_type'=>$viewType, 'rows'=>$rows ];
                }
            } elseif($viewType==='category_with_customers') {
                // Get customers present in orders for this date for this flag (products containing the flag)
                $custStmt = $this->pdo->prepare("SELECT DISTINCT cust.id,cust.name FROM orders o
                    JOIN order_items oi ON oi.order_id=o.id
                    JOIN products p ON p.id=oi.product_id
                    JOIN customers cust ON cust.id=o.customer_id
                    WHERE DATE(o.order_date)=? AND p.report_flags LIKE ? ORDER BY cust.name");
                $like = '%"'.$code.'"%';
                $custStmt->execute([$date,$like]);
                $customers = $custStmt->fetchAll();
                // Build product/customer matrix
                $sqlMatrix = "SELECT p.id as product_id,p.name,p.code,p.production_unit,c.name AS category_name,o.customer_id, SUM(oi.quantity) qty
                    FROM products p
                    LEFT JOIN categories c ON c.id=p.category_id
                    JOIN order_items oi ON oi.product_id=p.id
                    JOIN orders o ON o.id=oi.order_id
                    WHERE DATE(o.order_date)=? AND p.report_flags LIKE ?
                    ";
                if($includeIds){ $inPlace = implode(',',array_fill(0,count($includeIds),'?')); $sqlMatrix .= " AND p.id IN ($inPlace)"; }
                if($excludeIds){ $exPlace = implode(',',array_fill(0,count($excludeIds),'?')); $sqlMatrix .= " AND p.id NOT IN ($exPlace)"; }
                $sqlMatrix .= " GROUP BY p.id,o.customer_id ORDER BY c.name,p.name";
                $matrixStmt = $this->pdo->prepare($sqlMatrix);
                $params=[$date,$like]; $params=array_merge($params,$includeIds,$excludeIds); $matrixStmt->execute($params);
                $data=[]; // product_id => ['product'=>..., 'category'=>..., 'customers'=>[cust_id=>qty], 'total'=>sum]
                while($r=$matrixStmt->fetch(PDO::FETCH_ASSOC)){
                    $pid=$r['product_id'];
                    if(!isset($data[$pid])){ $data[$pid]=['product_id'=>$pid,'name'=>$r['name'],'code'=>$r['code'],'production_unit'=>$r['production_unit'],'category_name'=>$r['category_name'],'customers'=>[], 'total'=>0]; }
                    $data[$pid]['customers'][$r['customer_id']] = $r['qty'];
                    $data[$pid]['total'] += $r['qty'];
                }
                // Remove any zero-total products
                $data = array_filter($data, fn($r)=>($r['total']??0)>0);
                if(!empty($data)){
                    // Order data by category, name
                    usort($data,function($a,$b){ return [$a['category_name'],$a['name']] <=> [$b['category_name'],$b['name']]; });
                    $reports[] = [ 'flag'=>$flag, 'view_type'=>$viewType, 'customers'=>$customers, 'rows'=>$data ];
                }
            }
        }
    $wantAdditional = array_reduce($activeFlags,function($c,$f){ return $c || !empty($f['show_additional']); }, false);
    $wantBlocked = array_reduce($activeFlags,function($c,$f){ return $c || !empty($f['show_blocked']); }, false);
    // Additional products used today (only those actually ordered) if any flag wants it
    $additionalProducts = [];
    if($wantAdditional){
    $stmtAdd = $this->pdo->prepare("SELECT p.id as product_id,p.name AS product_name,cust.id as customer_id,cust.name AS customer_name, SUM(oi.quantity) qty
            FROM orders o
            JOIN order_items oi ON oi.order_id=o.id
            JOIN products p ON p.id=oi.product_id
            JOIN customer_additional_products cap ON cap.customer_id=o.customer_id AND cap.product_id=p.id
            JOIN customers cust ON cust.id=o.customer_id
            WHERE DATE(o.order_date)=?
            GROUP BY p.id,cust.id
            HAVING qty>0
            ORDER BY p.name,cust.name");
        $stmtAdd->execute([$date]);
        while($r=$stmtAdd->fetch(PDO::FETCH_ASSOC)){
            $pid=$r['product_id'];
            if(!isset($additionalProducts[$pid])){ $additionalProducts[$pid]=['product_id'=>$pid,'product_name'=>$r['product_name'],'customers'=>[], 'total'=>0]; }
            $additionalProducts[$pid]['customers'][$r['customer_id']] = ['name'=>$r['customer_name'],'qty'=>$r['qty']];
            $additionalProducts[$pid]['total'] += $r['qty'];
    } }
    // Blocked products that still appeared in orders (policy breach) if any flag wants it
    $blockedProducts = [];
    if($wantBlocked){
    $stmtBlk = $this->pdo->prepare("SELECT p.id as product_id,p.name AS product_name,cust.id as customer_id,cust.name AS customer_name, SUM(oi.quantity) qty
            FROM orders o
            JOIN order_items oi ON oi.order_id=o.id
            JOIN products p ON p.id=oi.product_id
            JOIN customer_blocked_products cbp ON cbp.customer_id=o.customer_id AND cbp.product_id=p.id
            JOIN customers cust ON cust.id=o.customer_id
            WHERE DATE(o.order_date)=?
            GROUP BY p.id,cust.id
            HAVING qty>0
            ORDER BY p.name,cust.name");
        $stmtBlk->execute([$date]);
        while($r=$stmtBlk->fetch(PDO::FETCH_ASSOC)){
            $pid=$r['product_id'];
            if(!isset($blockedProducts[$pid])){ $blockedProducts[$pid]=['product_id'=>$pid,'product_name'=>$r['product_name'],'customers'=>[], 'total'=>0]; }
            $blockedProducts[$pid]['customers'][$r['customer_id']] = ['name'=>$r['customer_name'],'qty'=>$r['qty']];
            $blockedProducts[$pid]['total'] += $r['qty'];
    } }
        if($export==='csv'){
            $this->exportCsv($date,$reports,$additionalProducts,$blockedProducts); return;
        } elseif($export==='pdf') {
            $this->exportPdf($date,$reports,$additionalProducts,$blockedProducts); return; // simple HTML download
        } elseif($export==='excel') {
            $this->exportExcel($date,$reports,$additionalProducts,$blockedProducts); return; // HTML tables with Excel mime
        }
        $this->view('reports/daily',[ 'date'=>$date,'reports'=>$reports,'additionalProducts'=>$additionalProducts,'blockedProducts'=>$blockedProducts ]);
    }
    private function exportCsv($date,$reports,$additionalProducts=[],$blockedProducts=[]){
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="daily_reports_'.$date.'.csv"');
        $out=fopen('php://output','w');
        foreach($reports as $rep){
            fputcsv($out,["Report",$rep['flag']['label'],"Code",$rep['flag']['code'],"Type",$rep['view_type']]);
            if($rep['view_type']==='category_summary'){
                fputcsv($out,['Category','Product','Qty','Unit']);
                foreach($rep['rows'] as $r){ fputcsv($out,[$r['category_name'],$r['name'],$r['qty'],$r['production_unit']??'']); }
            } else {
                $custs=$rep['customers'];
                $header=array_merge(['Category','Product'],array_map(fn($c)=>$c['name'],$custs),['Total','Unit']);
                fputcsv($out,$header);
                foreach($rep['rows'] as $r){
                    $row=[$r['category_name'],$r['name']];
                    foreach($custs as $c){ $row[] = $r['customers'][$c['id']] ?? 0; }
                    $row[]=$r['total'];
                    $row[]=$r['production_unit']??'';
                    fputcsv($out,$row);
                }
            }
            fputcsv($out,[]); // blank line
        }
        if(!empty($additionalProducts)){
            fputcsv($out,['Additional Products']);
            fputcsv($out,['Product','Customer','Qty']);
            foreach($additionalProducts as $ap){
                foreach($ap['customers'] as $cust){
                    fputcsv($out,[$ap['product_name'],$cust['name'],$cust['qty']]);
                }
                fputcsv($out,[$ap['product_name'],'TOTAL',$ap['total']]);
            }
            fputcsv($out,[]);
        }
        if(!empty($blockedProducts)){
            fputcsv($out,['Blocked Products']);
            fputcsv($out,['Product','Customer','Qty']);
            foreach($blockedProducts as $bp){
                foreach($bp['customers'] as $cust){
                    fputcsv($out,[$bp['product_name'],$cust['name'],$cust['qty']]);
                }
                fputcsv($out,[$bp['product_name'],'TOTAL',$bp['total']]);
            }
            fputcsv($out,[]);
        }
        fclose($out);
    }
    private function exportPdf($date,$reports,$additionalProducts=[],$blockedProducts=[]){
        // Lightweight approach: output printable HTML with content-type for PDF hint (browser can print to PDF)
        header('Content-Type: text/html; charset=utf-8');
        echo "<html><head><title>Daily Reports $date</title><style>body{font-family:Arial; font-size:12px;} table{border-collapse:collapse;width:100%;margin-bottom:25px;} th,td{border:1px solid #999;padding:4px;} th{background:#eee;} h2{margin-top:40px;} .cat{background:#ddd;font-weight:bold;}</style></head><body>";
        echo '<h1>Daily Reports '.htmlspecialchars($date).'</h1>';
        foreach($reports as $rep){
            echo '<h2>'.htmlspecialchars($rep['flag']['label']).' ('.htmlspecialchars($rep['flag']['view_type']).')</h2>';
            if($rep['view_type']==='category_summary'){
                $byCat=[]; foreach($rep['rows'] as $r){ $byCat[$r['category_name']][]=$r; }
                echo '<table><tr><th>Category</th><th>Product</th><th>Qty</th><th>Unit</th></tr>';
                foreach($byCat as $cat=>$rows){
                    foreach($rows as $i=>$r){
                        echo '<tr>'.($i==0?'<td rowspan="'.count($rows).'">'.htmlspecialchars($cat).'</td>':'').'<td>'.htmlspecialchars($r['name']).'</td><td style="text-align:right">'.($r['qty']+0).'</td><td>'.htmlspecialchars($r['production_unit']??'').'</td></tr>';
                    }
                }
                echo '</table>';
            } else {
                $custs=$rep['customers'];
                echo '<table><tr><th>Category</th><th>Product</th>';
                foreach($custs as $c){ echo '<th>'.htmlspecialchars($c['name']).'</th>'; }
                echo '<th>Total</th><th>Unit</th></tr>';
                foreach($rep['rows'] as $r){
                    echo '<tr><td>'.htmlspecialchars($r['category_name']).'</td><td>'.htmlspecialchars($r['name']).'</td>';
                    foreach($custs as $c){ $v=$r['customers'][$c['id']] ?? 0; echo '<td style="text-align:right">'.($v+0).'</td>'; }
                    echo '<td style="text-align:right">'.($r['total']+0).'</td><td>'.htmlspecialchars($r['production_unit']??'').'</td></tr>';
                }
                echo '</table>';
            }
        }
        if(!empty($additionalProducts)){
            echo '<h2>Additional Products (Ordered)</h2><table><tr><th>Product</th><th>Customer</th><th>Qty</th></tr>';
            foreach($additionalProducts as $ap){
                foreach($ap['customers'] as $cust){
                    echo '<tr><td>'.htmlspecialchars($ap['product_name']).'</td><td>'.htmlspecialchars($cust['name']).'</td><td style="text-align:right">'.($cust['qty']+0).'</td></tr>';
                }
                echo '<tr><td colspan="2" style="text-align:right;font-weight:bold;">TOTAL '.htmlspecialchars($ap['product_name']).'</td><td style="text-align:right">'.($ap['total']+0).'</td></tr>';
            }
            echo '</table>';
        }
        if(!empty($blockedProducts)){
            echo '<h2>Blocked Products (Ordered - Review)</h2><table><tr><th>Product</th><th>Customer</th><th>Qty</th></tr>';
            foreach($blockedProducts as $bp){
                foreach($bp['customers'] as $cust){
                    echo '<tr><td>'.htmlspecialchars($bp['product_name']).'</td><td>'.htmlspecialchars($cust['name']).'</td><td style="text-align:right">'.($cust['qty']+0).'</td></tr>';
                }
                echo '<tr><td colspan="2" style="text-align:right;font-weight:bold;">TOTAL '.htmlspecialchars($bp['product_name']).'</td><td style="text-align:right">'.($bp['total']+0).'</td></tr>';
            }
            echo '</table>';
        }
        echo '</body></html>';
    }
    private function exportExcel($date,$reports,$additionalProducts=[],$blockedProducts=[]){
        header('Content-Type: application/vnd.ms-excel; charset=utf-8');
        header('Content-Disposition: attachment; filename="daily_reports_'.$date.'.xls"');
        echo '<html><head><meta charset="utf-8"><style>table{border-collapse:collapse;} td,th{border:1px solid #666;padding:4px;} th{background:#eee;}</style></head><body>';
        echo '<h1>Daily Reports '.htmlspecialchars($date).'</h1>';
        foreach($reports as $rep){
            echo '<h2>'.htmlspecialchars($rep['flag']['label']).' ('.htmlspecialchars($rep['flag']['view_type']).')</h2>';
            if($rep['view_type']==='category_summary'){
                $byCat=[]; foreach($rep['rows'] as $r){ $byCat[$r['category_name']][]=$r; }
                echo '<table><tr><th>Category</th><th>Product</th><th>Qty</th><th>Unit</th></tr>';
                foreach($byCat as $cat=>$rows){
                    foreach($rows as $i=>$r){
                        echo '<tr>'.($i==0?'<td rowspan="'.count($rows).'">'.htmlspecialchars($cat).'</td>':'').'<td>'.htmlspecialchars($r['name']).'</td><td>'.($r['qty']+0).'</td><td>'.htmlspecialchars($r['production_unit']??'').'</td></tr>';
                    }
                }
                echo '</table>';
            } else {
                $custs=$rep['customers'];
                echo '<table><tr><th>Category</th><th>Product</th>';
                foreach($custs as $c){ echo '<th>'.htmlspecialchars($c['name']).'</th>'; }
                echo '<th>Total</th><th>Unit</th></tr>';
                foreach($rep['rows'] as $r){
                    echo '<tr><td>'.htmlspecialchars($r['category_name']).'</td><td>'.htmlspecialchars($r['name']).'</td>';
                    foreach($custs as $c){ $v=$r['customers'][$c['id']] ?? 0; echo '<td>'.($v+0).'</td>'; }
                    echo '<td>'.($r['total']+0).'</td><td>'.htmlspecialchars($r['production_unit']??'').'</td></tr>';
                }
                echo '</table>';
            }
        }
        if(!empty($additionalProducts)){
            echo '<h2>Additional Products (Ordered)</h2><table><tr><th>Product</th><th>Customer</th><th>Qty</th></tr>';
            foreach($additionalProducts as $ap){
                foreach($ap['customers'] as $cust){
                    echo '<tr><td>'.htmlspecialchars($ap['product_name']).'</td><td>'.htmlspecialchars($cust['name']).'</td><td>'.($cust['qty']+0).'</td></tr>';
                }
                echo '<tr><td colspan="2" style="text-align:right;font-weight:bold;">TOTAL '.htmlspecialchars($ap['product_name']).'</td><td>'.($ap['total']+0).'</td></tr>';
            }
            echo '</table>';
        }
        if(!empty($blockedProducts)){
            echo '<h2>Blocked Products (Ordered - Review)</h2><table><tr><th>Product</th><th>Customer</th><th>Qty</th></tr>';
            foreach($blockedProducts as $bp){
                foreach($bp['customers'] as $cust){
                    echo '<tr><td>'.htmlspecialchars($bp['product_name']).'</td><td>'.htmlspecialchars($cust['name']).'</td><td>'.($cust['qty']+0).'</td></tr>';
                }
                echo '<tr><td colspan="2" style="text-align:right;font-weight:bold;">TOTAL '.htmlspecialchars($bp['product_name']).'</td><td>'.($bp['total']+0).'</td></tr>';
            }
            echo '</table>';
        }
        echo '</body></html>';
    }
    private function view($v,$d=[]){ extract($d); require_once "../app/Views/$v.php"; }
}
