
<?php
include 'php/db_connection.php';

// ---------------------------------------------
// 1) দৈনিক বিক্রি (আজকের)
// ---------------------------------------------
$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) সর্বমোট বিক্রি
// ---------------------------------------------
$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) সর্বমোট ক্রয়
// ---------------------------------------------
$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) লাভ / লোকসান (বিক্রি থেকে প্রফিট – খরচ বাদ দিয়ে)
// ---------------------------------------------
$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;

// খরচ
$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) মোট কাস্টমার
// ---------------------------------------------
$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) মোট কাস্টমার ডিউ (sales টেবিল থেকে)
// ---------------------------------------------
$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;
//-------------------------মোট কাস্টমার ডিউ +---------------------
$due =  $total_customer_due - $total_customer_due_collected_amount ;

// ---------------------------------------------
// 7) মোট সাপ্লায়ার
// ---------------------------------------------
$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) মোট সাপ্লায়ার ডিউ (supplier_bills টেবিল থেকে)
// ---------------------------------------------
$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 & 10 & 11) প্রডাক্ট (রিটার্ন সহ যোগ হবে)
// - মোট আইটেম = Product_id গুনে দেখা
// - মোট Quantity = Quantity + Return Quantity
// ---------------------------------------------
$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'];
}
// ---------------------------------------------
// প্রডাক্ট মেয়াদ নাই
// ---------------------------------------------
$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'];
}

// ---------------------------------------------
// প্রডাক্ট বিক্রয় করা মোট
// ---------------------------------------------
$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_items = (int)$row['total_items'];
    $total_products_qty11 = (int)$row['total_qty'];
}

// যদি return টেবিল থাকে, রিটার্ন যোগ করব
$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;

$total_products_qty += $return_qty;

$f_product= $total_products_qty - $total_products_qty11 ;
?>

<!-- ================= Dashboard Cards ================= -->
<style>
.dashboard-cards {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(220px, 1fr));
    gap: 15px;
    margin-top: 20px;
}
.card {
    background: #fff;
    border-radius: 12px;
    padding: 20px;
    text-align: center;
    box-shadow: 0 4px 8px rgba(0,0,0,0.1);
    font-size: 18px;
    font-weight: bold;
}
.card span {
    display: block;
    font-size: 22px;
    margin-top: 8px;
}
.card.daily { border-left: 6px solid #20c997; }
.card.sales { border-left: 6px solid #28a745; }
.card.purchase { border-left: 6px solid #007bff; }
.card.cost { border-left: 6px solid #ffc107; }
.card.products { border-left: 6px solid #17a2b8; }
.card.products_sales { border-left: 6px solid #043841ff; }
.card.items { border-left: 6px solid #6c757d; }
.card.customers { border-left: 6px solid #6610f2; }
.card.suppliers { border-left: 6px solid #e83e8c; }
.card.profit { border-left: 6px solid #6f42c1; }
.card.loss { border-left: 6px solid #dc3545; }
</style>
<?php include 'sidebar.php'; ?>
<div class="dashboard-cards">
    <div class="card daily">
        Daily Sales 
        <span><?php echo number_format($daily_sales,2); ?> Tk</span>
    </div>
    <div class="card sales">
        Total Sales 
        <span><?php echo number_format($total_sales,2); ?> Tk</span>
    </div>
    <div class="card products_sales">
        Sales Profit 
        <span><?php echo $total_profit_from_sales; ?> Tk</span>
    </div>
    <div class="card cost">
        Total Expenses 
        <span><?php echo number_format($total_cost,2); ?> Tk</span>
    </div>
    
    <?php if($profit_loss >= 0) { ?>
        <div class="card profit">
            Profit
            <span style="color:green;"><?php echo number_format($profit_loss,2); ?> Tk</span>
        </div>
    <?php } else { ?>
        <div class="card loss">
            Loss
            <span style="color:red;"><?php echo number_format(abs($profit_loss),2); ?> Tk</span>
        </div>
    <?php } ?>
    <div class="card purchase">
        Total Purchase
        <span><?php echo number_format($total_purchase,2); ?> Tk</span>
    </div>
    <div class="card customers">
        Total Customers
        <span><?php echo $total_customers; ?></span>
    </div>
    <div class="card customers">
        Customer Due
        <span><?php echo number_format($due,2); ?> Tk</span>
    </div>
    <div class="card suppliers">
        Total Suppliers 
        <span><?php echo $total_suppliers; ?></span>
    </div>
    <div class="card suppliers">
        Supplier Due  
        <span><?php echo number_format($total_supplier_due,2); ?> Tk</span>
    </div>
    <div class="card items">
        Total Product Items 
        <span><?php echo $total_items; ?></span>
    </div>
    <div class="card products">
        Total Product Quantity 
        <span><?php echo $total_products_qty; ?></span>
    </div>
    <div class="card products_sales">
        Total Sold Quantity
        <span><?php echo $total_products_qty11; ?></span>
    </div>
    <div class="card products_sales">
        Expired Products
        <span><?php echo  $total_expired_qty; ?></span>
    </div>
    <!-------- বন্ধ-----------
    <div class="card products">
        স্টক প্রডাক্ট পরিমাণ 
        <span><?php /* echo $f_product;  */?></span>
    </div>     ------------>
    
</div>
 <div class="recent-sales mt-5">
            <?php include 'resent_sales.php';
                  include 'footer.php';
            ?>
        </div>
