<?php
require('fpdf/fpdf.php');
require('db.php');

/* --------- INPUT (safe defaults, supports POST/GET) --------- */
$start = $_POST['start_date'] ?? $_GET['start_date'] ?? $_GET['start'] ?? date('Y-m-01');
$end   = $_POST['end_date']   ?? $_GET['end_date']   ?? $_GET['end']   ?? date('Y-m-t');
$start = date('Y-m-d', strtotime($start));
$end   = date('Y-m-d', strtotime($end));

/* --------- PDF CLASS WITH WRAPPING TABLE HELPERS --------- */
class PDF extends FPDF {
    var $widths;
    var $aligns;

    function Header() {
        // Company header
        $this->SetFont('Arial','B',15);
        $this->Cell(0,8,'VSONIC ENGINEERING - Monthly Financial Report',0,1,'C');
        $this->SetFont('Arial','',11);
        global $start, $end;
        $this->Cell(0,7, 'Period: '.date('d M Y', strtotime($start)).' to '.date('d M Y', strtotime($end)), 0, 1, 'C');
        $this->Ln(3);
    }

    function Footer() {
        $this->SetY(-15);
        $this->SetFont('Arial','I',8);
        $this->Cell(0,10,'Page '.$this->PageNo().'/{nb}',0,0,'C');
    }

    function SetWidths($w){ $this->widths=$w; }
    function SetAligns($a){ $this->aligns=$a; }

    function CheckPageBreak($h){
        if($this->GetY()+$h > $this->PageBreakTrigger)
            $this->AddPage($this->CurOrientation);
    }

    // count lines a MultiCell of width w will take
    function NbLines($w,$txt){
        $cw=&$this->CurrentFont['cw'];
        if($w==0) $w=$this->w-$this->rMargin-$this->x;
        $wmax=($w-2*$this->cMargin)*1000/$this->FontSize;
        $s=str_replace("\r",'',$txt);
        $nb=strlen($s);
        if($nb>0 && $s[$nb-1]=="\n") $nb--;
        $sep=-1; $i=0; $j=0; $l=0; $nl=1;
        while($i<$nb){
            $c=$s[$i];
            if($c=="\n"){ $i++; $sep=-1; $j=$i; $l=0; $nl++; continue; }
            if($c==' ') $sep=$i;
            $l+=$cw[$c] ?? 0;
            if($l>$wmax){
                if($sep==-1){
                    if($i==$j) $i++;
                } else $i=$sep+1;
                $sep=-1; $j=$i; $l=0; $nl++;
            } else $i++;
        }
        return $nl;
    }

    // Draw a row with wrapped cells
    function Row($data, $borders = true){
        $nb=0;
        for($i=0;$i<count($data);$i++)
            $nb=max($nb,$this->NbLines($this->widths[$i], (string)$data[$i]));
        $h=6*$nb;
        $this->CheckPageBreak($h);
        for($i=0;$i<count($data);$i++){
            $w=$this->widths[$i];
            $a=isset($this->aligns[$i]) ? $this->aligns[$i] : 'L';
            $x=$this->GetX(); $y=$this->GetY();
            if($borders) $this->Rect($x,$y,$w,$h);
            $this->MultiCell($w,6,(string)$data[$i],0,$a);
            $this->SetXY($x+$w,$y);
        }
        $this->Ln($h);
    }

    function SectionTitle($txt){
        $this->SetFillColor(230,230,230);
        $this->SetFont('Arial','B',11);
        $this->Cell(0,8,$txt,0,1,'L',true);
        $this->Ln(1);
    }

    function TableHeader($headers, $widths, $aligns=null){
        $this->SetFont('Arial','B',9);
        $this->SetWidths($widths);
        if($aligns) $this->SetAligns($aligns);
        for($i=0;$i<count($headers);$i++){
            $this->Cell($widths[$i],8,$headers[$i],1,0,'C');
        }
        $this->Ln();
        $this->SetFont('Arial','',9);
    }
}

/* --------- START PDF --------- */
$pdf = new PDF('L','mm','A4');   // Landscape
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetFont('Arial','',9);

$grand_total = 0.00;

/* --------- QUOTATIONS (join items) --------- */
$pdf->SectionTitle('Quotations');
$pdf->TableHeader(
    ['ID','Partner','Date','Total (RM)'],
    [18, 170, 35, 35],
    ['C','L','C','R']
);
$q1 = "
    SELECT 
        q.id,
        q.partner_name,
        COALESCE(q.date_created, q.date_created) AS date_created,
        SUM(qi.quantity * qi.unit_price) AS total
    FROM quotations q
    JOIN quotation_items qi ON q.id = qi.quotation_id
    WHERE COALESCE(q.date_created, q.date_created) BETWEEN '$start' AND '$end'
    GROUP BY q.id, q.partner_name, date_created
    ORDER BY date_created ASC
";
$r1 = $conn->query($q1);
$sub_q = 0.00;
if($r1 && $r1->num_rows){
    while($row = $r1->fetch_assoc()){
        $pdf->Row([
            $row['id'],
            $row['partner_name'],
            $row['date_created'],
            number_format((float)$row['total'],2)
        ]);
        $sub_q += (float)$row['total'];
    }
} else {
    $pdf->Row(['-', 'No records for selected range', '-', '-']);
}
$pdf->SetFont('Arial','B',9);
$pdf->Cell(223,8,'Subtotal (Quotations)',1,0,'R');
$pdf->Cell(35,8,number_format($sub_q,2),1,1,'R');
$pdf->Ln(3);
$grand_total += $sub_q;
$pdf->SetFont('Arial','',9);

/* --------- INVOICES (join items) --------- */
$pdf->SectionTitle('Invoices');
$pdf->TableHeader(
    ['ID','Partner','Date','Total (RM)'],
    [18, 170, 35, 35],
    ['C','L','C','R']
);
$q2 = "
    SELECT 
        i.id,
        i.partner_name,
        COALESCE(i.created_at, i.created_at) AS date_created,
        SUM(ii.quantity * ii.unit_price) AS total
    FROM invoices i
    JOIN invoice_items ii ON i.id = ii.invoice_id
    WHERE COALESCE(i.created_at, i.created_at) BETWEEN '$start' AND '$end'
    GROUP BY i.id, i.partner_name, date_created
    ORDER BY date_created ASC
";
$r2 = $conn->query($q2);
$sub_i = 0.00;
if($r2 && $r2->num_rows){
    while($row = $r2->fetch_assoc()){
        $pdf->Row([
            $row['id'],
            $row['partner_name'],
            $row['date_created'],
            number_format((float)$row['total'],2)
        ]);
        $sub_i += (float)$row['total'];
    }
} else {
    $pdf->Row(['-', 'No records for selected range', '-', '-']);
}
$pdf->SetFont('Arial','B',9);
$pdf->Cell(223,8,'Subtotal (Invoices)',1,0,'R');
$pdf->Cell(35,8,number_format($sub_i,2),1,1,'R');
$pdf->Ln(3);
$grand_total += $sub_i;
$pdf->SetFont('Arial','',9);

/* --------- EMPLOYEE PAYMENTS (expenses) --------- */
$pdf->SectionTitle('Employee Payments');
$pdf->TableHeader(
    ['ID','Employee','Date','Method','Amount (RM)','Remarks'],
    [16, 80, 30, 40, 22, 70],        // adjusted widths to fit landscape (~258)
    ['C','L','C','L','R','L']
);

$q3 = "
    SELECT p.id, e.name AS employee, p.payment_date, p.method, p.amount, p.remarks
    FROM payments p
    JOIN employees e ON p.employee_id = e.id
    WHERE p.payment_date BETWEEN '$start' AND '$end'
    ORDER BY p.payment_date ASC, p.id ASC
";
$r3 = $conn->query($q3);

$sub_p = 0.00;
if($r3 && $r3->num_rows){
    while($row = $r3->fetch_assoc()){
        $pdf->Row([
            $row['id'],
            $row['employee'],
            $row['payment_date'],
            $row['method'],
            number_format((float)$row['amount'],2),
            $row['remarks'] ?? ''
        ]);
        $sub_p += (float)$row['amount'];
    }
} else {
    $pdf->Row(['-','No records for selected range','-','-','-','-']);
}

$pdf->SetFont('Arial','B',9);
$pdf->Cell(188,8,'Subtotal (Employee Payments)',1,0,'R'); // 16+80+30+40+22 = 188
$pdf->Cell(70,8,number_format($sub_p,2),1,1,'R');         // last column "Remarks" width
$pdf->Ln(4);

$grand_total += $sub_p;


/* --------- GRAND TOTAL --------- */
$pdf->SetFont('Arial','B',11);
$pdf->Cell(0,10,'Grand Total (All Groups): RM '.number_format($grand_total,2),0,1,'R');

/* --------- LOG THE PRINT (no output!) --------- */
$user = 'Admin'; // TODO: replace with your logged-in user
$logSql = "INSERT INTO print_logs (user, start_date, end_date, created_at) VALUES (?,?,?,NOW())";
if ($stmt = $conn->prepare($logSql)) {
    $stmt->bind_param('sss', $user, $start, $end);
    $stmt->execute();
}

/* --------- OUTPUT --------- */
$pdf->Output('I', 'monthly_report.pdf');
