<?php

class FinanceAccount extends Model
{
    protected string $table = 'finance_accounts';

    public function allActive(): array
    {
        return $this->db->query(
            "SELECT * FROM finance_accounts ORDER BY is_active DESC, account_name ASC"
        )->fetchAll();
    }

    public function totalBalance(): float
    {
        $stmt = $this->db->query("SELECT COALESCE(SUM(current_balance),0) as total FROM finance_accounts WHERE is_active = 1");
        return (float) $stmt->fetch()['total'];
    }

    public function negativeBalanceAccounts(): array
    {
        return $this->db->query(
            "SELECT * FROM finance_accounts WHERE is_active = 1 AND current_balance < 0"
        )->fetchAll();
    }

    /**
     * Manual ledger entry — e.g. opening balance, owner cash injection, misc income/expense
     * not tied to an order or a specific purchase.
     */
    public function manualEntry(int $accountId, float $amount, string $type, string $category, string $description, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $delta = $type === 'CREDIT' ? $amount : -$amount;
            $this->db->prepare("UPDATE finance_accounts SET current_balance = current_balance + :delta WHERE id = :id")
                ->execute(['delta' => $delta, 'id' => $accountId]);

            $this->db->prepare(
                "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, description, created_by)
                 VALUES (:account_id, :type, :amount, :category, 'MANUAL', :description, :user_id)"
            )->execute([
                'account_id'  => $accountId,
                'type'        => $type,
                'amount'      => $amount,
                'category'    => $category,
                'description' => $description ?: null,
                'user_id'     => $userId,
            ]);

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