<?php
session_start();
include 'php/db_connection.php';
require('pdf/fpdf.php');

if(!isset($_SESSION['user_id'])){
    header("Location: login.php");
    exit();
}

date_default_timezone_set('Asia/Dhaka');

// Get filter range from GET or default 30 days
$days = isset($_GET['days']) ? intval($_GET['days']) : 30;
$start_date = date('Y-m-d', strtotime("-$days days"));
$end_date = date('Y-m-d');

// ---------------------------------------------
// 1) Daily Sales (Today's)
// ---------------------------------------------
$sql_daily = "
    SELECT IFNULL(SUM(sd.price * sd.Quantity),0) AS daily_sales
    FROM sales_items sd
    WHERE DATE(sd.created_at) = CURDATE()
";
$q_daily = $con->query($sql_daily);
$daily_sales = ($q_daily && $row = $q_daily->fetch_assoc()) ? (float)$row['daily_sales'] : 0;

// ---------------------------------------------
// 2) Total Sales
// ---------------------------------------------
$sql2 = "SELECT IFNULL(SUM(sd.price * sd.Quantity),0) AS total_sales FROM sales_items sd";
$q2 = $con->query($sql2);
$total_sales = ($q2 && $row = $q2->fetch_assoc()) ? (float)$row['total_sales'] : 0;

// ---------------------------------------------
// 3) Total Purchase
// ---------------------------------------------
$sql1 = "
    SELECT IFNULL(SUM(pc.purchase_price * p.Quantity),0) AS total_purchase
    FROM product p
    LEFT JOIN product_cat pc ON p.Product_id = pc.Product_id
";
$q1 = $con->query($sql1);
$total_purchase = ($q1 && $row = $q1->fetch_assoc()) ? (float)$row['total_purchase'] : 0;

// ---------------------------------------------
// 4) Profit / Loss
// ---------------------------------------------
$sql5 = "
    SELECT IFNULL(SUM(sd.Quantity * (sd.price - pc.purchase_price)),0) AS total_profit
    FROM sales_items sd
    LEFT JOIN product_cat pc ON sd.Product_id = pc.Product_id
";
$q5 = $con->query($sql5);
$total_profit_from_sales = ($q5 && $row = $q5->fetch_assoc()) ? (float)$row['total_profit'] : 0;

// Total Cost
$sql3 = "SELECT IFNULL(SUM(tk),0) AS total_cost FROM cost";
$q3 = $con->query($sql3);
$total_cost = ($q3 && $row = $q3->fetch_assoc()) ? (float)$row['total_cost'] : 0;

$profit_loss = $total_profit_from_sales - $total_cost;

// ---------------------------------------------
// 5) Total Customers
// ---------------------------------------------
$sql6 = "SELECT COUNT(*) AS total_customers FROM customer";
$q6 = $con->query($sql6);
$total_customers = ($q6 && $row = $q6->fetch_assoc()) ? (int)$row['total_customers'] : 0;

// ---------------------------------------------
// 6) Total Customer Due
// ---------------------------------------------
$sql7 = "SELECT IFNULL(SUM(final_due),0) AS total_customer_due FROM sales";
$q7 = $con->query($sql7);
$total_customer_due = ($q7 && $row = $q7->fetch_assoc()) ? (float)$row['total_customer_due'] : 0;

$sql77 = "SELECT IFNULL(SUM(collected_amount),0) AS total_customer_due_collected_amount FROM due_collections";
$q77 = $con->query($sql77);
$total_customer_due_collected_amount = ($q77 && $row = $q77->fetch_assoc()) ? (float)$row['total_customer_due_collected_amount'] : 0;

$customer_due = $total_customer_due - $total_customer_due_collected_amount;

// ---------------------------------------------
// 7) Total Suppliers
// ---------------------------------------------
$sql8 = "SELECT COUNT(*) AS total_suppliers FROM suppliers";
$q8 = $con->query($sql8);
$total_suppliers = ($q8 && $row = $q8->fetch_assoc()) ? (int)$row['total_suppliers'] : 0;

// ---------------------------------------------
// 8) Total Supplier Due
// ---------------------------------------------
$sql9 = "SELECT IFNULL(SUM(due_amount),0) AS total_supplier_due FROM supplier_bills";
$q9 = $con->query($sql9);
$total_supplier_due = ($q9 && $row = $q9->fetch_assoc()) ? (float)$row['total_supplier_due'] : 0;

// ---------------------------------------------
// 9) Product Information
// ---------------------------------------------
$sql10 = "SELECT COUNT(DISTINCT Product_id) AS total_items, IFNULL(SUM(Quantity),0) AS total_qty FROM product";
$q10 = $con->query($sql10);
if($q10 && $row = $q10->fetch_assoc()) {
    $total_items = (int)$row['total_items'];
    $total_products_qty = (int)$row['total_qty'];
}

// Expired Products
$sql_expired = "
    SELECT 
        COUNT(DISTINCT Product_id) AS total_expired_items,
        IFNULL(SUM(Quantity), 0) AS total_expired_qty
    FROM product
    WHERE Expiration_date <= CURDATE()
";
$q_expired = $con->query($sql_expired);
if($q_expired && $row = $q_expired->fetch_assoc()) {
    $total_expired_items = (int)$row['total_expired_items'];
    $total_expired_qty = (int)$row['total_expired_qty'];
}

// Sold Products
$sql11 = "SELECT COUNT(DISTINCT Product_id) AS total_items, IFNULL(SUM(Quantity),0) AS total_qty FROM sales_items";
$q11 = $con->query($sql11);
if($q11 && $row = $q11->fetch_assoc()) {
    $total_sold_items = (int)$row['total_items'];
    $total_sold_qty = (int)$row['total_qty'];
}

// Returns
$sql_return = "SELECT IFNULL(SUM(return_quantity),0) AS return_qty FROM returns";
$q_return = $con->query($sql_return);
$return_qty = ($q_return && $row = $q_return->fetch_assoc()) ? (int)$row['return_qty'] : 0;

// ---------------------------------------------
// Daily Sales Data (Date-wise)
// ---------------------------------------------
$result = $con->query("
    SELECT 
        DATE(s.created_at) as sale_date,
        COUNT(DISTINCT s.id) as total_sales_count,
        SUM(s.grand_total) as grand_total,
        SUM(s.collected) as total_collected,
        SUM(s.final_due) as total_due,
        SUM(si.Quantity * (si.price - pc.purchase_price)) as daily_profit
    FROM sales s
    LEFT JOIN sales_items si ON s.id = si.sales_id
    LEFT JOIN product_cat pc ON si.Product_id = pc.Product_id
    WHERE s.created_at BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'
    GROUP BY DATE(s.created_at)
    ORDER BY DATE(s.created_at) ASC
");

// Create PDF
class PDF extends FPDF {
    function Header(){
        $this->SetFont('Arial','B',16);
        $this->Cell(0,10,'M/S Taqwa Medicine Shop',0,1,'C');
        $this->Cell(0,10,'Business Report',0,1,'C');
        $this->SetFont('Arial','',10);
        $this->Cell(0,5,'Generated: '.date('d-m-Y h:i A'),0,1,'C');
        $this->Ln(5);
    }

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

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

    function InfoRow($label, $value, $color_red = false){
        $this->SetFont('Arial','',10);
        $this->Cell(100,7,$label,1,0,'L');
        if($color_red){
            $this->SetTextColor(255,0,0);
        } else {
            $this->SetTextColor(0,128,0);
        }
        $this->Cell(90,7,$value,1,1,'R');
        $this->SetTextColor(0,0,0);
    }
}

$pdf = new PDF();
$pdf->AliasNbPages();
$pdf->AddPage();

// ============ Sales Information ============
$pdf->SectionTitle('Sales Information');
$pdf->InfoRow('Daily Sales', number_format($daily_sales,2).' Tk');
$pdf->InfoRow('Total Sales', number_format($total_sales,2).' Tk');
$pdf->InfoRow('Sales Profit', number_format($total_profit_from_sales,2).' Tk');
$pdf->Ln(5);

// ============ Purchase & Expenses ============
$pdf->SectionTitle('Purchase & Expenses');
$pdf->InfoRow('Total Purchase', number_format($total_purchase,2).' Tk');
$pdf->InfoRow('Total Expenses', number_format($total_cost,2).' Tk');
$pdf->Ln(5);

// ============ Profit/Loss ============
$pdf->SectionTitle('Profit/Loss');
if($profit_loss >= 0){
    $pdf->InfoRow('Net Profit', number_format($profit_loss,2).' Tk');
} else {
    $pdf->InfoRow('Net Loss', number_format(abs($profit_loss),2).' Tk', true);
}
$pdf->Ln(5);

// ============ Customer Information ============
$pdf->SectionTitle('Customer Information');
$pdf->InfoRow('Total Customers', $total_customers);
$pdf->InfoRow('Customer Due', number_format($customer_due,2).' Tk', true);
$pdf->Ln(5);

// ============ Supplier Information ============
$pdf->SectionTitle('Supplier Information');
$pdf->InfoRow('Total Suppliers', $total_suppliers);
$pdf->InfoRow('Supplier Due', number_format($total_supplier_due,2).' Tk', true);
$pdf->Ln(5);

// ============ Product Information ============
$pdf->SectionTitle('Product Information');
$pdf->InfoRow('Total Product Items', $total_items);
$pdf->InfoRow('Total Product Quantity', $total_products_qty);
$pdf->InfoRow('Total Sold Quantity', $total_sold_qty);
$pdf->InfoRow('Expired Products', $total_expired_qty, true);
$pdf->InfoRow('Returned Products', $return_qty);
$pdf->Ln(5);

// ============ Daily Sales Breakdown ============
$pdf->AddPage();
$pdf->SectionTitle('Daily Sales Breakdown - Last '.$days.' Days');
$pdf->SetFont('Arial','B',9);
$pdf->Cell(30,8,'Date',1,0,'C');
$pdf->Cell(25,8,'Sales',1,0,'C');
$pdf->Cell(35,8,'Total',1,0,'C');
$pdf->Cell(35,8,'Collected',1,0,'C');
$pdf->Cell(30,8,'Due',1,0,'C');
$pdf->Cell(35,8,'Profit',1,1,'C');

$pdf->SetFont('Arial','',8);
while($row = $result->fetch_assoc()){
    $pdf->Cell(30,7,date('d-m-Y', strtotime($row['sale_date'])),1,0,'C');
    $pdf->Cell(25,7,$row['total_sales_count'],1,0,'C');
    $pdf->Cell(35,7,number_format($row['grand_total'],2),1,0,'R');
    $pdf->Cell(35,7,number_format($row['total_collected'],2),1,0,'R');
    $pdf->Cell(30,7,number_format($row['total_due'],2),1,0,'R');
    
    $daily_profit = $row['daily_profit'];
    if($daily_profit >= 0){
        $pdf->SetTextColor(0,128,0);
    } else {
        $pdf->SetTextColor(255,0,0);
    }
    $pdf->Cell(35,7,number_format($daily_profit,2),1,1,'R');
    $pdf->SetTextColor(0,0,0);
}

$pdf->Output('I','business_report.pdf');
?>