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

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

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

try {
    $today      = date('Y-m-d');
    $week_start = date('Y-m-d', strtotime('-6 days'));
    $month_start= date('Y-m-d', strtotime('-29 days'));
    $this_month = date('Y-m-01');

    // ── ১. আজকের summary ─────────────────────────────────────────────
    $todayQ = $pdo->prepare(
        "SELECT
            SUM(CASE WHEN direction='IN'  AND is_deleted=0 THEN amount ELSE 0 END) AS today_in,
            SUM(CASE WHEN direction='OUT' AND is_deleted=0 THEN amount ELSE 0 END) AS today_out
         FROM cash_transactions WHERE transaction_date = :d"
    );
    $todayQ->execute([':d' => $today]);
    $todayData = $todayQ->fetch(PDO::FETCH_ASSOC);

    // ── ২. এই মাসের বিক্রি summary ───────────────────────────────────
    $monthSumQ = $pdo->prepare(
        "SELECT
            COUNT(*)            AS cnt,
            SUM(grand_total)    AS total,
            SUM(paid_amount)    AS paid,
            SUM(due_amount)     AS due
         FROM barcode_sales
         WHERE DATE(sale_date) >= :ms AND deleted_at IS NULL"
    );
    $monthSumQ->execute([':ms' => $this_month]);
    $monthSum = $monthSumQ->fetch(PDO::FETCH_ASSOC);

    // ── ৩. সাপ্তাহিক বিক্রি (৭ দিন) ─────────────────────────────────
    $weekQ = $pdo->prepare(
        "SELECT
            DATE(sale_date) AS sdate,
            COUNT(*)        AS cnt,
            SUM(grand_total) AS total
         FROM barcode_sales
         WHERE DATE(sale_date) BETWEEN :ws AND :we
           AND deleted_at IS NULL
         GROUP BY DATE(sale_date)
         ORDER BY sdate ASC"
    );
    $weekQ->execute([':ws' => $week_start, ':we' => $today]);
    $weekly = $weekQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৪. মাসিক বিক্রি (৩০ দিন) ────────────────────────────────────
    $monthQ = $pdo->prepare(
        "SELECT
            DATE(sale_date) AS sdate,
            COUNT(*)        AS cnt,
            SUM(grand_total) AS total
         FROM barcode_sales
         WHERE DATE(sale_date) BETWEEN :ms AND :me
           AND deleted_at IS NULL
         GROUP BY DATE(sale_date)
         ORDER BY sdate ASC"
    );
    $monthQ->execute([':ms' => $month_start, ':me' => $today]);
    $monthly = $monthQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৫. Cash IN vs OUT (৩০ দিন) ───────────────────────────────────
    $cashQ = $pdo->prepare(
        "SELECT
            transaction_date AS tdate,
            SUM(CASE WHEN direction='IN'  AND is_deleted=0 THEN amount ELSE 0 END) AS cash_in,
            SUM(CASE WHEN direction='OUT' AND is_deleted=0 THEN amount ELSE 0 END) AS cash_out
         FROM cash_transactions
         WHERE transaction_date BETWEEN :ms AND :me
         GROUP BY transaction_date
         ORDER BY tdate ASC"
    );
    $cashQ->execute([':ms' => $month_start, ':me' => $today]);
    $cashFlow = $cashQ->fetchAll(PDO::FETCH_ASSOC);

    // মোট ৩০ দিনের IN/OUT
    $total30In  = array_sum(array_column($cashFlow, 'cash_in'));
    $total30Out = array_sum(array_column($cashFlow, 'cash_out'));

    // ── ৬. বন্ধক সারসংক্ষেপ ──────────────────────────────────────────
    $pledgeQ = $pdo->query(
        "SELECT
            COUNT(*)                      AS total_active,
            SUM(outstanding_principal)    AS total_principal,
            SUM(CASE WHEN DATEDIFF(CURDATE(), pledge_date) > 30
                AND NOT EXISTS (
                    SELECT 1 FROM pawn_payments pp
                    WHERE pp.pledge_id = pawn_pledges.id
                    AND pp.payment_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                ) THEN 1 ELSE 0 END)      AS overdue_count
         FROM pawn_pledges WHERE status = 'OPEN'"
    );
    $pledge = $pledgeQ->fetch(PDO::FETCH_ASSOC);

    // সুদ হিসাব
    $int_total = 0;
    $pledgeList = $pdo->query("SELECT outstanding_principal, interest_rate, pledge_date FROM pawn_pledges WHERE status='OPEN'")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($pledgeList as $pl) {
        $days = (int)(new DateTime($pl['pledge_date']))->diff(new DateTime($today))->days;
        $int_total += (float)$pl['outstanding_principal'] * ((float)$pl['interest_rate']/100) * ($days/30);
    }

    // ── ৭. Karat ভিত্তিক স্টক ────────────────────────────────────────
    $karatQ = $pdo->query(
        "SELECT karat, COUNT(*) AS cnt, SUM(gross_weight_gram) AS gross_gram
         FROM barcode_stock
         WHERE status='in_stock' AND is_deleted=0
         GROUP BY karat ORDER BY karat"
    );
    $karatStock = $karatQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৮. Account balances ───────────────────────────────────────────
    $accQ = $pdo->query(
        "SELECT COALESCE(account_name_bn, account_name) AS name, account_type, current_balance
         FROM cash_accounts WHERE is_active=1 AND deleted_at IS NULL ORDER BY sort_order, id"
    );
    $accounts = $accQ->fetchAll(PDO::FETCH_ASSOC);
    $total_balance = array_sum(array_column($accounts, 'current_balance'));

    echo json_encode([
        'success'     => true,
        'today'       => [
            'cash_in'  => (float)($todayData['today_in']  ?? 0),
            'cash_out' => (float)($todayData['today_out'] ?? 0),
            'net'      => (float)(($todayData['today_in'] ?? 0) - ($todayData['today_out'] ?? 0)),
        ],
        'this_month'  => [
            'cnt'   => (int)($monthSum['cnt']   ?? 0),
            'total' => (float)($monthSum['total'] ?? 0),
            'paid'  => (float)($monthSum['paid']  ?? 0),
            'due'   => (float)($monthSum['due']   ?? 0),
        ],
        'weekly'      => $weekly,
        'monthly'     => $monthly,
        'cash_flow'   => $cashFlow,
        'cash_30'     => [
            'total_in'  => (float)$total30In,
            'total_out' => (float)$total30Out,
            'net'       => (float)($total30In - $total30Out),
        ],
        'pledge'      => [
            'active'          => (int)($pledge['total_active']    ?? 0),
            'principal'       => (float)($pledge['total_principal'] ?? 0),
            'overdue'         => (int)($pledge['overdue_count']   ?? 0),
            'interest_due'    => round($int_total, 2),
        ],
        'karat_stock' => $karatStock,
        'accounts'    => $accounts,
        'total_balance' => (float)$total_balance,
    ]);

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