<?php

class PurchaseOrder extends Model
{
    protected string $table = 'purchase_orders';

    public function generatePoNumber(): string
    {
        $today = date('Ymd');
        $stmt = $this->db->query("SELECT COUNT(*) as cnt FROM purchase_orders WHERE DATE(created_at) = CURDATE()");
        $countToday = (int) $stmt->fetch()['cnt'] + 1;
        return sprintf('PO-%s-%04d', $today, $countToday);
    }

    /**
     * Create a PO with line items. $items = [['raw_material_id'=>, 'quantity'=>, 'unit_cost'=>], ...]
     */
    public function createWithItems(int $supplierId, array $items, ?string $expectedDate, string $notes, ?int $userId): int
    {
        $this->db->beginTransaction();
        try {
            $subtotal = 0;
            foreach ($items as $item) {
                $subtotal += $item['quantity'] * $item['unit_cost'];
            }

            $poId = $this->insert([
                'po_number'     => $this->generatePoNumber(),
                'supplier_id'   => $supplierId,
                'status'        => 'ORDERED',
                'order_date'    => date('Y-m-d'),
                'expected_date' => $expectedDate ?: null,
                'subtotal'      => $subtotal,
                'notes'         => $notes ?: null,
                'created_by'    => $userId,
            ]);

            $stmt = $this->db->prepare(
                "INSERT INTO purchase_order_items (purchase_order_id, raw_material_id, quantity_ordered, unit_cost, line_total)
                 VALUES (:po_id, :material_id, :qty, :cost, :line_total)"
            );
            foreach ($items as $item) {
                $stmt->execute([
                    'po_id'       => $poId,
                    'material_id' => $item['raw_material_id'],
                    'qty'         => $item['quantity'],
                    'cost'        => $item['unit_cost'],
                    'line_total'  => $item['quantity'] * $item['unit_cost'],
                ]);
            }

            $this->db->commit();
            return $poId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function findWithItems(int $poId): array|false
    {
        $stmt = $this->db->prepare(
            "SELECT po.*, s.supplier_name, s.phone as supplier_phone, u.full_name as created_by_name
             FROM purchase_orders po
             JOIN suppliers s ON s.id = po.supplier_id
             LEFT JOIN users u ON u.id = po.created_by
             WHERE po.id = :id LIMIT 1"
        );
        $stmt->execute(['id' => $poId]);
        $po = $stmt->fetch();
        if (!$po) {
            return false;
        }

        $itemStmt = $this->db->prepare(
            "SELECT poi.*, rm.material_name, rm.unit
             FROM purchase_order_items poi JOIN raw_materials rm ON rm.id = poi.raw_material_id
             WHERE poi.purchase_order_id = :id"
        );
        $itemStmt->execute(['id' => $poId]);
        $po['items'] = $itemStmt->fetchAll();

        $paymentStmt = $this->db->prepare(
            "SELECT sp.*, fa.account_name FROM supplier_payments sp
             JOIN finance_accounts fa ON fa.id = sp.finance_account_id
             WHERE sp.purchase_order_id = :id ORDER BY sp.paid_at DESC"
        );
        $paymentStmt->execute(['id' => $poId]);
        $po['payments'] = $paymentStmt->fetchAll();

        return $po;
    }

    public function listWithSupplier(string $status = ''): array
    {
        $sql = "SELECT po.*, s.supplier_name
                FROM purchase_orders po JOIN suppliers s ON s.id = po.supplier_id WHERE 1=1";
        $params = [];
        if ($status !== '' && $status !== 'ALL') {
            $sql .= " AND po.status = :status";
            $params['status'] = $status;
        }
        $sql .= " ORDER BY po.id DESC LIMIT 100";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function listFiltered(string $status = '', string $search = '', string $dateFrom = '', string $dateTo = ''): array
    {
        $sql = "SELECT po.*, s.supplier_name
                FROM purchase_orders po JOIN suppliers s ON s.id = po.supplier_id WHERE 1=1";
        $params = [];
        if ($status !== '' && $status !== 'ALL') {
            $sql .= " AND po.status = :status";
            $params['status'] = $status;
        }
        if ($search !== '') {
            $sql .= " AND (po.po_number LIKE :s1 OR s.supplier_name LIKE :s2)";
            $params['s1'] = "%{$search}%";
            $params['s2'] = "%{$search}%";
        }
        if ($dateFrom !== '') {
            $sql .= " AND po.order_date >= :date_from";
            $params['date_from'] = $dateFrom;
        }
        if ($dateTo !== '') {
            $sql .= " AND po.order_date <= :date_to";
            $params['date_to'] = $dateTo;
        }
        $sql .= " ORDER BY po.id DESC LIMIT 200";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function summaryStats(string $dateFrom = '', string $dateTo = ''): array
    {
        $sql = "SELECT COUNT(*) as order_count, COALESCE(SUM(subtotal),0) as total_purchased, COALESCE(SUM(paid_amount),0) as total_paid
                FROM purchase_orders WHERE status != 'CANCELLED'";
        $params = [];
        if ($dateFrom !== '') { $sql .= " AND order_date >= :date_from"; $params['date_from'] = $dateFrom; }
        if ($dateTo !== '') { $sql .= " AND order_date <= :date_to"; $params['date_to'] = $dateTo; }
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch();
    }

    /**
     * Receive goods against a PO. $receipts = [purchase_order_item_id => quantity_received_now, ...]
     * Updates raw_materials stock, logs stock_transactions, and recalculates PO status.
     */
    public function receiveItems(int $poId, array $receipts, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $itemStmt = $this->db->prepare("SELECT * FROM purchase_order_items WHERE id = :id AND purchase_order_id = :po_id");
            $updateReceivedStmt = $this->db->prepare(
                "UPDATE purchase_order_items SET quantity_received = quantity_received + :qty WHERE id = :id"
            );
            $stockUpdateStmt = $this->db->prepare(
                "UPDATE raw_materials SET current_stock = current_stock + :qty WHERE id = :id"
            );
            $stockTxnStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:material_id, 'PURCHASE_IN', :qty, 'PURCHASE_ORDER', :po_id, :notes, :user_id)"
            );

            $poRow = $this->find($poId);

            foreach ($receipts as $itemId => $qty) {
                $qty = (float) $qty;
                if ($qty <= 0) {
                    continue;
                }
                $itemStmt->execute(['id' => $itemId, 'po_id' => $poId]);
                $item = $itemStmt->fetch();
                if (!$item) {
                    continue;
                }

                $updateReceivedStmt->execute(['qty' => $qty, 'id' => $itemId]);
                $stockUpdateStmt->execute(['qty' => $qty, 'id' => $item['raw_material_id']]);
                $stockTxnStmt->execute([
                    'material_id' => $item['raw_material_id'],
                    'qty'         => $qty,
                    'po_id'       => $poId,
                    'notes'       => "Received against {$poRow['po_number']}",
                    'user_id'     => $userId,
                ]);
            }

            // Recalculate PO status based on total ordered vs received
            $totals = $this->db->prepare(
                "SELECT COALESCE(SUM(quantity_ordered),0) as ordered, COALESCE(SUM(quantity_received),0) as received
                 FROM purchase_order_items WHERE purchase_order_id = :id"
            );
            $totals->execute(['id' => $poId]);
            $t = $totals->fetch();

            $status = 'ORDERED';
            if ((float) $t['received'] >= (float) $t['ordered'] && (float) $t['ordered'] > 0) {
                $status = 'RECEIVED';
            } elseif ((float) $t['received'] > 0) {
                $status = 'PARTIALLY_RECEIVED';
            }
            $this->update($poId, ['status' => $status]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function cancel(int $poId): void
    {
        $this->update($poId, ['status' => 'CANCELLED']);
    }

    public function recordPayment(int $poId, float $amount, int $financeAccountId, string $notes, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $po = $this->find($poId);
            if (!$po) {
                throw new RuntimeException('Purchase order not found');
            }

            $this->db->prepare(
                "INSERT INTO supplier_payments (supplier_id, purchase_order_id, amount, finance_account_id, notes, created_by)
                 VALUES (:supplier_id, :po_id, :amount, :account_id, :notes, :user_id)"
            )->execute([
                'supplier_id' => $po['supplier_id'],
                'po_id'       => $poId,
                'amount'      => $amount,
                'account_id'  => $financeAccountId,
                'notes'       => $notes ?: null,
                'user_id'     => $userId,
            ]);

            $this->update($poId, ['paid_amount' => (float) $po['paid_amount'] + $amount]);

            $this->db->prepare(
                "UPDATE finance_accounts SET current_balance = current_balance - :amt WHERE id = :id"
            )->execute(['amt' => $amount, 'id' => $financeAccountId]);

            $this->db->prepare(
                "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, reference_id, description, created_by)
                 VALUES (:account_id, 'DEBIT', :amount, 'PURCHASE', 'PURCHASE_ORDER', :po_id, :description, :user_id)"
            )->execute([
                'account_id'  => $financeAccountId,
                'amount'      => $amount,
                'po_id'       => $poId,
                'description' => "Payment for {$po['po_number']}",
                'user_id'     => $userId,
            ]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}
