<?php
/**
 * Item Controller — Items/Jins CRUD
 */

class ItemController {
    private PDO $db;
    private object $user;

    public function __construct(PDO $db, object $user) {
        $this->db = $db;
        $this->user = $user;
    }

    /** GET /items */
    public function index(array $query): void {
        $sql = "SELECT * FROM items WHERE is_active = 1 ORDER BY name ASC";
        $stmt = $this->db->query($sql);
        Response::success($stmt->fetchAll());
    }

    /** POST /items */
    public function store(array $data): void {
        Validator::check($data, [
            'name' => 'required|string|max:255',
            'unit' => 'nullable|string|max:50'
        ]);

        $stmt = $this->db->prepare("INSERT INTO items (name, unit) VALUES (?, ?)");
        $stmt->execute([$data['name'], $data['unit'] ?? 'KG']);
        $id = (int)$this->db->lastInsertId();

        AuditLogger::log($this->db, $this->user->id, 'CREATED_ITEM', 'items', $id, null, $data);

        Response::created(['id' => $id], 'جنس کامیابی سے شامل کی گئی۔');
    }

    /** PUT /items/{id} */
    public function update(int $id, array $data): void {
        $stmt = $this->db->prepare("SELECT * FROM items WHERE id = ?");
        $stmt->execute([$id]);
        if (!$stmt->fetch()) Response::notFound('آئٹم نہیں ملی۔');

        $fields = [];
        $params = [];
        if (isset($data['name'])) { $fields[] = 'name = ?'; $params[] = $data['name']; }
        if (isset($data['unit'])) { $fields[] = 'unit = ?'; $params[] = $data['unit']; }
        if (isset($data['is_active'])) { $fields[] = 'is_active = ?'; $params[] = (int)$data['is_active']; }

        if (empty($fields)) Response::error('Nothing to update.', 400);

        $params[] = $id;
        $this->db->prepare("UPDATE items SET " . implode(', ', $fields) . " WHERE id = ?")->execute($params);

        Response::success(null, 'آئٹم اپڈیٹ ہو گئی۔');
    }

    /** DELETE /items/{id} */
    public function destroy(int $id): void {
        $stmt = $this->db->prepare("SELECT * FROM items WHERE id = ?");
        $stmt->execute([$id]);
        if (!$stmt->fetch()) Response::notFound('آئٹم نہیں ملی۔');

        // Soft delete: set is_active = 0
        $this->db->prepare("UPDATE items SET is_active = 0 WHERE id = ?")->execute([$id]);

        AuditLogger::log($this->db, $this->user->id, 'DELETED_ITEM', 'items', $id, null, ['is_active' => 0]);

        Response::success(null, 'آئٹم کامیابی سے حذف ہو گئی۔');
    }

    /** GET /items/{id}/advanced-ledger */
    public function advancedLedger(int $itemId, array $query): void {
        $fromDate = $query['from_date'] ?? date('Y-m-01');
        $toDate = $query['to_date'] ?? date('Y-m-d');

        // 1. Verify item exists
        $stmt = $this->db->prepare("SELECT * FROM items WHERE id = ?");
        $stmt->execute([$itemId]);
        $item = $stmt->fetch();
        if (!$item) Response::notFound('آئٹم نہیں ملی۔');

        // 2. O(1) Opening Balance (Sum before fromDate)
        // Purchases (Inward)
        $openingInStmt = $this->db->prepare("
            SELECT COALESCE(SUM(pl.net_weight), 0) as opening_in
            FROM purchase_lots pl
            JOIN purchase_master pm ON pl.purchase_id = pm.id
            WHERE pl.item_id = ? AND pm.voucher_date < ? AND pm.is_reversed = 0
        ");
        $openingInStmt->execute([$itemId, $fromDate]);
        $openingIn = floatval($openingInStmt->fetchColumn());

        // Sales (Outward)
        $openingOutStmt = $this->db->prepare("
            SELECT COALESCE(SUM(si.weight), 0) as opening_out
            FROM sale_items si
            JOIN sale_master sm ON si.sale_id = sm.id
            WHERE si.item_id = ? AND sm.voucher_date < ? AND sm.is_reversed = 0
        ");
        $openingOutStmt->execute([$itemId, $fromDate]);
        $openingOut = floatval($openingOutStmt->fetchColumn());

        $openingWazan = round($openingIn - $openingOut, 2);

        // Opening Financial Balance: (Purchase + Expense) - Sale amounts before fromDate
        // Purchase Amount: Proportional net_payable based on gross_amount
        $openingPurchaseAmtStmt = $this->db->prepare("
            SELECT COALESCE(SUM(IF(pm.total_gross_amount > 0, ROUND(pm.net_payable * (pl.gross_amount / pm.total_gross_amount), 2), 0)), 0)
            FROM purchase_lots pl
            JOIN purchase_master pm ON pl.purchase_id = pm.id
            WHERE pl.item_id = ? AND pm.voucher_date < ? AND pm.is_reversed = 0
        ");
        $openingPurchaseAmtStmt->execute([$itemId, $fromDate]);
        $openingPurchaseAmt = floatval($openingPurchaseAmtStmt->fetchColumn());

        // Sale Amount: Proportional net_receivable based on gross_amount
        $openingSaleAmtStmt = $this->db->prepare("
            SELECT COALESCE(SUM(IF(sm.gross_amount > 0, ROUND(sm.net_receivable * (si.gross_amount / sm.gross_amount), 2), 0)), 0)
            FROM sale_items si
            JOIN sale_master sm ON si.sale_id = sm.id
            WHERE si.item_id = ? AND sm.voucher_date < ? AND sm.is_reversed = 0
        ");
        $openingSaleAmtStmt->execute([$itemId, $fromDate]);
        $openingSaleAmt = floatval($openingSaleAmtStmt->fetchColumn());

        $openingExpenseAmtStmt = $this->db->prepare("
            SELECT COALESCE(SUM(j.total_amount), 0)
            FROM journals j
            WHERE j.source_type = 'expense' AND j.source_id = ? AND j.source_id IS NOT NULL AND j.journal_date < ? AND j.is_reversed = 0 AND j.narration NOT LIKE 'REVERSAL%'
        ");
        $openingExpenseAmtStmt->execute([$itemId, $fromDate]);
        $openingExpenseAmt = floatval($openingExpenseAmtStmt->fetchColumn());

        $openingFinancial = round(($openingPurchaseAmt + $openingExpenseAmt) - $openingSaleAmt, 2);

        // 3. UNION ALL Query for Date Range Data
        $unionSql = "
            SELECT 
                pm.voucher_date as date, pm.voucher_no as ref_no, 'purchase' as type, 'آمد' as type_label,
                a.name as party_name, pl.net_weight as weight_in, 0 as weight_out,
                IF(pm.total_gross_amount > 0, ROUND(pm.net_payable * (pl.gross_amount / pm.total_gross_amount), 2), 0) as amount, pm.id as master_id
            FROM purchase_lots pl
            JOIN purchase_master pm ON pl.purchase_id = pm.id
            JOIN accounts a ON pm.zamidar_id = a.id
            WHERE pl.item_id = ? AND pm.voucher_date BETWEEN ? AND ? AND pm.is_reversed = 0

            UNION ALL

            SELECT 
                sm.voucher_date as date, sm.voucher_no as ref_no, 'sale' as type, 'نکاس' as type_label,
                a.name as party_name, 0 as weight_in, si.weight as weight_out,
                IF(sm.gross_amount > 0, ROUND(sm.net_receivable * (si.gross_amount / sm.gross_amount), 2), 0) as amount, sm.id as master_id
            FROM sale_items si
            JOIN sale_master sm ON si.sale_id = sm.id
            JOIN accounts a ON sm.khareedar_id = a.id
            WHERE si.item_id = ? AND sm.voucher_date BETWEEN ? AND ? AND sm.is_reversed = 0

            UNION ALL

            SELECT 
                j.journal_date as date, CONCAT('J-', j.id) as ref_no, 'expense' as type, 'خرچہ' as type_label,
                j.narration as party_name, 0 as weight_in, 0 as weight_out,
                j.total_amount as amount, j.id as master_id
            FROM journals j
            WHERE j.source_type = 'expense' AND j.source_id = ? AND j.source_id IS NOT NULL AND j.journal_date BETWEEN ? AND ? AND j.is_reversed = 0 AND j.narration NOT LIKE 'REVERSAL%'

            ORDER BY date ASC, type ASC, master_id ASC
        ";

        $stmt = $this->db->prepare($unionSql);
        $stmt->execute([
            $itemId, $fromDate, $toDate,
            $itemId, $fromDate, $toDate,
            $itemId, $fromDate, $toDate
        ]);
        $entries = $stmt->fetchAll();

        // 4. Calculate Running Balances (Wazan + Financial)
        $runningWazan = $openingWazan;
        $runningFinancial = $openingFinancial;
        $totalIn = 0;
        $totalOut = 0;
        $totalDr = 0;
        $totalCr = 0;

        foreach ($entries as &$entry) {
            $in = floatval($entry['weight_in']);
            $out = floatval($entry['weight_out']);
            $amt = floatval($entry['amount']);

            // Wazan balance
            $runningWazan += ($in - $out);
            $entry['running_wazan'] = round($runningWazan, 2);

            $totalIn += $in;
            $totalOut += $out;

            // Financial Dr/Cr logic
            if ($entry['type'] === 'purchase' || $entry['type'] === 'expense') {
                $entry['dr'] = round($amt, 2);
                $entry['cr'] = 0;
                $runningFinancial += $amt;
                $totalDr += $amt;
            } else {
                // sale
                $entry['dr'] = 0;
                $entry['cr'] = round($amt, 2);
                $runningFinancial -= $amt;
                $totalCr += $amt;
            }
            $entry['running_financial'] = round($runningFinancial, 2);
        }

        Response::success([
            'item' => $item,
            'opening_wazan' => $openingWazan,
            'opening_financial' => $openingFinancial,
            'entries' => $entries,
            'summary' => [
                'total_in' => round($totalIn, 2),
                'total_out' => round($totalOut, 2),
                'total_dr' => round($totalDr, 2),
                'total_cr' => round($totalCr, 2),
                'current_stock' => round($runningWazan, 2),
                'current_financial' => round($runningFinancial, 2)
            ]
        ]);
    }
}
