<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/reports/purchase_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');

    // ── ১. purchases টেবিল summary ───────────────────────────────────
    $pSumQ = $pdo->prepare(
        "SELECT
            COUNT(*)              AS cnt,
            SUM(p.gross_weight)   AS gross_gram,
            SUM(p.pure_weight)    AS pure_gram,
            SUM(p.total_amount)   AS total_amt,
            SUM(p.paid_amount)    AS paid_amt,
            SUM(p.due_amount)     AS due_amt
         FROM purchases p
         WHERE p.purchase_date BETWEEN :df AND :dt
           AND p.deleted_at IS NULL"
    );
    $pSumQ->execute([':df' => $date_from, ':dt' => $date_to]);
    $pSum = $pSumQ->fetch(PDO::FETCH_ASSOC);

    // ── ২. purchase_stock টেবিল summary ──────────────────────────────
    $psSumQ = $pdo->prepare(
        "SELECT
            COUNT(*)              AS cnt,
            SUM(ps.gross_weight)  AS gross_gram,
            SUM(ps.pure_weight)   AS pure_gram,
            SUM(ps.total_amount)  AS total_amt
         FROM purchase_stock ps
         WHERE ps.purchase_date BETWEEN :df AND :dt
           AND ps.deleted_at IS NULL"
    );
    $psSumQ->execute([':df' => $date_from, ':dt' => $date_to]);
    $psSum = $psSumQ->fetch(PDO::FETCH_ASSOC);

    // ── ৩. purchases — karat breakdown ───────────────────────────────
    $pKatQ = $pdo->prepare(
        "SELECT
            p.karat,
            COUNT(*)            AS cnt,
            SUM(p.gross_weight) AS gross_gram,
            SUM(p.pure_weight)  AS pure_gram,
            SUM(p.total_amount) AS total_amt
         FROM purchases p
         WHERE p.purchase_date BETWEEN :df AND :dt
           AND p.deleted_at IS NULL
         GROUP BY p.karat ORDER BY p.karat"
    );
    $pKatQ->execute([':df' => $date_from, ':dt' => $date_to]);
    $pKarat = $pKatQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৪. purchases — item list ──────────────────────────────────────
    $pListQ = $pdo->prepare(
        "SELECT
            p.id,
            p.purchase_date,
            p.item_name,
            p.karat,
            p.gross_weight,
            p.pure_weight,
            p.actual_purity,
            p.total_pieces,
            p.rate_per_vori_24k AS rate_per_vori,
            p.total_amount,
            p.paid_amount,
            p.due_amount,
            p.payment_status,
            p.note,
            COALESCE(pt.name,'') AS supplier_name
         FROM purchases p
         LEFT JOIN parties pt ON pt.id = p.party_id
         WHERE p.purchase_date BETWEEN :df AND :dt
           AND p.deleted_at IS NULL
         ORDER BY p.purchase_date DESC, p.id DESC"
    );
    $pListQ->execute([':df' => $date_from, ':dt' => $date_to]);
    $purchases = $pListQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৫. purchase_stock — item list ─────────────────────────────────
    $psListQ = $pdo->prepare(
        "SELECT
            ps.id,
            ps.purchase_date,
            ps.item_name,
            ps.karat,
            ps.gross_weight,
            ps.pure_weight,
            ps.total_pieces,
            ps.rate_per_vori,
            ps.total_amount,
            ps.note,
            COALESCE(pt.name,'') AS supplier_name
         FROM purchase_stock ps
         LEFT JOIN parties pt ON pt.id = ps.party_id
         WHERE ps.purchase_date BETWEEN :df AND :dt
           AND ps.deleted_at IS NULL
         ORDER BY ps.purchase_date DESC, ps.id DESC"
    );
    $psListQ->execute([':df' => $date_from, ':dt' => $date_to]);
    $purchase_stock = $psListQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৬. দৈনিক combined summary ────────────────────────────────────
    $dailyQ = $pdo->prepare(
        "SELECT tdate, SUM(day_total) AS day_total, SUM(day_cnt) AS day_cnt
         FROM (
           SELECT purchase_date AS tdate, SUM(total_amount) AS day_total, COUNT(*) AS day_cnt
           FROM purchases
           WHERE purchase_date BETWEEN :df AND :dt AND deleted_at IS NULL
           GROUP BY purchase_date
           UNION ALL
           SELECT purchase_date AS tdate, SUM(total_amount) AS day_total, COUNT(*) AS day_cnt
           FROM purchase_stock
           WHERE purchase_date BETWEEN :df2 AND :dt2 AND deleted_at IS NULL
           GROUP BY purchase_date
         ) combined
         GROUP BY tdate
         ORDER BY tdate ASC"
    );
    $dailyQ->execute([':df'=>$date_from,':dt'=>$date_to,':df2'=>$date_from,':dt2'=>$date_to]);
    $daily = $dailyQ->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode([
        'success'        => true,
        'date_from'      => $date_from,
        'date_to'        => $date_to,
        'summary'        => [
            'total_cnt'        => (int)($pSum['cnt']??0) + (int)($psSum['cnt']??0),
            'total_gross_gram' => (float)($pSum['gross_gram']??0) + (float)($psSum['gross_gram']??0),
            'total_pure_gram'  => (float)($pSum['pure_gram']??0)  + (float)($psSum['pure_gram']??0),
            'total_amount'     => (float)($pSum['total_amt']??0)  + (float)($psSum['total_amt']??0),
            'paid_amount'      => (float)($pSum['paid_amt']??0),
            'due_amount'       => (float)($pSum['due_amt']??0),
        ],
        'karat_breakdown' => $pKarat,
        'daily'           => $daily,
        'purchases'       => $purchases,
        'purchase_stock'  => $purchase_stock,
    ]);

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