<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/reports/sales_report.php

require_once __DIR__ . '/../../api/_core.php';
require_once __DIR__ . '/../../config/db.php';

header('Content-Type: application/json');

try {
    $date_from     = $_GET['date_from']     ?? date('Y-m-01');
    $date_to       = $_GET['date_to']       ?? date('Y-m-d');
    $status_filter = $_GET['status']        ?? '';

    $where  = "DATE(bs.sale_date) BETWEEN :df AND :dt AND bs.deleted_at IS NULL";
    $params = [':df' => $date_from, ':dt' => $date_to];

    if ($status_filter) {
        $where .= " AND bs.status = :st";
        $params[':st'] = $status_filter;
    }

    // ── ১. Summary ────────────────────────────────────────────────────
    $sumQ = $pdo->prepare(
        "SELECT
            COUNT(*)                    AS total_invoices,
            SUM(bs.grand_total)         AS total_grand,
            SUM(bs.paid_amount)         AS total_paid,
            SUM(bs.due_amount)          AS total_due,
            SUM(bs.items_total)         AS total_items,
            SUM(bs.making_charge)       AS total_making,
            SUM(bs.discount)            AS total_discount,
            SUM(bs.vat_amount)          AS total_vat,
            SUM(bs.exchange_amount)     AS total_exchange
         FROM barcode_sales bs
         WHERE $where"
    );
    $sumQ->execute($params);
    $summary = $sumQ->fetch(PDO::FETCH_ASSOC);

    // ── ২. Status breakdown ───────────────────────────────────────────
    $stQ = $pdo->prepare(
        "SELECT bs.status, COUNT(*) AS cnt, SUM(bs.grand_total) AS total
         FROM barcode_sales bs
         WHERE $where
         GROUP BY bs.status"
    );
    $stQ->execute($params);
    $status_breakdown = $stQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৩. দৈনিক summary ─────────────────────────────────────────────
    $dailyQ = $pdo->prepare(
        "SELECT
            DATE(bs.sale_date)      AS sdate,
            COUNT(*)                AS cnt,
            SUM(bs.grand_total)     AS day_total,
            SUM(bs.paid_amount)     AS day_paid,
            SUM(bs.due_amount)      AS day_due
         FROM barcode_sales bs
         WHERE $where
         GROUP BY DATE(bs.sale_date)
         ORDER BY sdate ASC"
    );
    $dailyQ->execute($params);
    $daily = $dailyQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৪. Invoice list (items সহ) ────────────────────────────────────
    $invQ = $pdo->prepare(
        "SELECT
            bs.id,
            bs.invoice_no,
            bs.sale_date,
            bs.customer_name,
            bs.customer_phone,
            bs.customer_type,
            bs.items_total,
            bs.making_charge,
            bs.discount,
            bs.vat_amount,
            bs.exchange_amount,
            bs.grand_total,
            bs.paid_amount,
            bs.due_amount,
            bs.status,
            bs.notes
         FROM barcode_sales bs
         WHERE $where
         ORDER BY bs.sale_date DESC, bs.id DESC
         LIMIT 500"
    );
    $invQ->execute($params);
    $invoices = $invQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৫. প্রতিটা invoice-এর items ──────────────────────────────────
    $sale_ids = array_column($invoices, 'id');
    $items_map = [];

    if ($sale_ids) {
        $in = implode(',', array_map('intval', $sale_ids));
        $itemsQ = $pdo->query(
            "SELECT
                bsi.sale_id,
                bsi.item_name,
                bsi.karat,
                bsi.weight_g,
                bsi.weight_vori,
                bsi.sale_rate_per_vori,
                bsi.item_price,
                bsi.barcode
             FROM barcode_sale_items bsi
             WHERE bsi.sale_id IN ($in)
             ORDER BY bsi.sale_id, bsi.id"
        );
        while ($row = $itemsQ->fetch(PDO::FETCH_ASSOC)) {
            $items_map[$row['sale_id']][] = $row;
        }
    }

    // items attach করি
    foreach ($invoices as &$inv) {
        $inv['items'] = $items_map[$inv['id']] ?? [];
    }
    unset($inv);

    echo json_encode([
        'success'          => true,
        'date_from'        => $date_from,
        'date_to'          => $date_to,
        'summary'          => array_map('floatval_recursive', $summary),
        'status_breakdown' => $status_breakdown,
        'daily'            => $daily,
        'invoices'         => $invoices,
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}

function floatval_recursive($val) {
    return is_numeric($val) ? (float)$val : $val;
}