<?php
require "db.php";

function esc($v){
    return htmlspecialchars($v ?? '', ENT_QUOTES, 'UTF-8');
}

/* =====================================================
   HARD DELETE (ONLY DRAFT / PENDING)
===================================================== */
if(isset($_GET['delete']) && is_numeric($_GET['delete'])){

    $sale_id = (int)$_GET['delete'];

    $check = mysqli_fetch_assoc(mysqli_query($conn,"
        SELECT status FROM hps_sales WHERE id=$sale_id
    "));

    if($check && ($check['status']=='Draft' || $check['status']=='Pending')){

        mysqli_begin_transaction($conn);

        // Restore stock
        $items = mysqli_query($conn,"
            SELECT product_id, qty
            FROM hps_sale_items
            WHERE sale_id=$sale_id
        ");

        while($it = mysqli_fetch_assoc($items)){
            mysqli_query($conn,"
                UPDATE hps_stock
                SET qty_on_hand = qty_on_hand + ".$it['qty']."
                WHERE product_id=".$it['product_id']."
            ");
        }

        // Delete child records
        mysqli_query($conn,"DELETE FROM hps_installment_receipts WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_installment_schedule WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_payment_schedule WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_payments WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_customer_ledger WHERE ref_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_cashbook WHERE ref_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_approvals WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_checklist WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_documents WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_sale_items WHERE sale_id=$sale_id");

        // Delete sale
        mysqli_query($conn,"DELETE FROM hps_sales WHERE id=$sale_id");

        mysqli_commit($conn);

        header("Location: hps_profit_report.php");
        exit;
    }
}


/* =====================================================
   CANCEL APPROVED SALE (FINANCIAL SAFE)
===================================================== */
if(isset($_GET['cancel']) && is_numeric($_GET['cancel'])){

    $sale_id = (int)$_GET['cancel'];

    $check = mysqli_fetch_assoc(mysqli_query($conn,"
        SELECT status FROM hps_sales WHERE id=$sale_id
    "));

    if($check && $check['status']=='Approved'){

        mysqli_begin_transaction($conn);

        // Restore stock
        $items = mysqli_query($conn,"
            SELECT product_id, qty
            FROM hps_sale_items
            WHERE sale_id=$sale_id
        ");

        while($it = mysqli_fetch_assoc($items)){
            mysqli_query($conn,"
                UPDATE hps_stock
                SET qty_on_hand = qty_on_hand + ".$it['qty']."
                WHERE product_id=".$it['product_id']."
            ");
        }

        // Remove financial data
        mysqli_query($conn,"DELETE FROM hps_installment_receipts WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_installment_schedule WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_payment_schedule WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_payments WHERE sale_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_customer_ledger WHERE ref_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_cashbook WHERE ref_id=$sale_id");
        mysqli_query($conn,"DELETE FROM hps_approvals WHERE sale_id=$sale_id");

        // Mark cancelled
        mysqli_query($conn,"
            UPDATE hps_sales
            SET status='Cancelled'
            WHERE id=$sale_id
        ");

        mysqli_commit($conn);

        header("Location: hps_profit_report.php");
        exit;
    }
}


/* =====================================================
   FILTERS
===================================================== */
$from   = $_GET['from'] ?? '';
$to     = $_GET['to'] ?? '';
$status = $_GET['status'] ?? '';

$where = "s.status!='Cancelled'";

if ($from)   $where .= " AND s.sale_date >= '".mysqli_real_escape_string($conn,$from)."'";
if ($to)     $where .= " AND s.sale_date <= '".mysqli_real_escape_string($conn,$to)."'";
if ($status) $where .= " AND s.status='".mysqli_real_escape_string($conn,$status)."'";


/* =====================================================
   PROFIT QUERY
===================================================== */
$sql = "
SELECT
    s.id,
    s.sale_no,
    s.sale_date,
    s.status,
    c.name AS customer,
    c.cnic,
    SUM(i.qty * i.price) AS sale_amount,
    SUM(i.qty * IFNULL(st.avg_cost,0)) AS cost_amount
FROM hps_sales s
JOIN hps_customers c ON c.id=s.customer_id
JOIN hps_sale_items i ON i.sale_id=s.id
LEFT JOIN hps_stock st ON st.product_id=i.product_id
WHERE $where
GROUP BY s.id
ORDER BY s.sale_date DESC
";

$res = mysqli_query($conn,$sql);


/* =====================================================
   TOTALS
===================================================== */
$totSale = $totCost = $totProfit = 0;
$rows = [];

while($r=mysqli_fetch_assoc($res)){
    $r['profit'] = $r['sale_amount'] - $r['cost_amount'];
    $totSale   += $r['sale_amount'];
    $totCost   += $r['cost_amount'];
    $totProfit += $r['profit'];
    $rows[] = $r;
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Profit Report</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">

<style>
body{background:#f1f3f5}
.report-card{
    max-width:1500px;
    margin:30px auto;
    padding:25px;
    background:#fff;
    border-radius:20px;
    box-shadow:0 10px 25px rgba(0,0,0,.12);
}
@media print{
    .no-print{display:none}
    body{background:#fff}
}
</style>
</head>
<body>

<div class="report-card">

<h4 class="mb-3">💰 Profit Report (Cost vs Sale)</h4>

<form class="row g-2 mb-3 no-print">

<div class="col-md-2">
<label class="small">From</label>
<input type="date" name="from" value="<?=esc($from)?>" class="form-control form-control-sm">
</div>

<div class="col-md-2">
<label class="small">To</label>
<input type="date" name="to" value="<?=esc($to)?>" class="form-control form-control-sm">
</div>

<div class="col-md-2">
<label class="small">Status</label>
<select name="status" class="form-select form-select-sm">
<option value="">All</option>
<?php foreach(['Draft','Pending','Approved','Completed'] as $s): ?>
<option value="<?=$s?>" <?=($status==$s)?'selected':''?>><?=$s?></option>
<?php endforeach; ?>
</select>
</div>

<div class="col-md-2 d-flex align-items-end">
<button class="btn btn-primary btn-sm w-100">Filter</button>
</div>

<div class="col-md-2 d-flex align-items-end">
<button type="button" onclick="window.print()" class="btn btn-secondary btn-sm w-100">
Print
</button>
</div>

</form>

<div class="row mb-3">
<div class="col-md-4"><b>Total Sale:</b> <?=number_format($totSale)?></div>
<div class="col-md-4"><b>Total Cost:</b> <?=number_format($totCost)?></div>
<div class="col-md-4 text-success"><b>Total Profit:</b> <?=number_format($totProfit)?></div>
</div>

<table class="table table-bordered table-sm">
<thead class="table-dark">
<tr>
<th>Sale No</th>
<th>Date</th>
<th>Customer</th>
<th>CNIC</th>
<th>Status</th>
<th class="text-end">Sale</th>
<th class="text-end">Cost</th>
<th class="text-end">Profit</th>
<th class="no-print text-center">Action</th>
</tr>
</thead>
<tbody>

<?php if(empty($rows)): ?>
<tr><td colspan="9" class="text-center text-muted">No records</td></tr>
<?php endif; ?>

<?php foreach($rows as $r): ?>
<tr>
<td><?=esc($r['sale_no'])?></td>
<td><?=esc($r['sale_date'])?></td>
<td><?=esc($r['customer'])?></td>
<td><?=esc($r['cnic'])?></td>
<td><?=esc($r['status'])?></td>
<td class="text-end"><?=number_format($r['sale_amount'])?></td>
<td class="text-end"><?=number_format($r['cost_amount'])?></td>
<td class="text-end <?=($r['profit']>=0?'text-success':'text-danger')?>"><?=number_format($r['profit'])?></td>

<td class="no-print text-center">

<?php if($r['status']=='Draft' || $r['status']=='Pending'): ?>
<a href="?delete=<?=$r['id']?>"
   class="btn btn-sm btn-danger"
   onclick="return confirm('Delete this sale?')">
   Delete
</a>

<?php elseif($r['status']=='Approved'): ?>
<a href="?cancel=<?=$r['id']?>"
   class="btn btn-sm btn-warning"
   onclick="return confirm('Cancel this approved sale?')">
   Cancel
</a>

<?php else: ?>
<span class="text-muted">Locked</span>
<?php endif; ?>

</td>

</tr>
<?php endforeach; ?>

</tbody>
</table>

</div>
</body>
</html>
