<?php
class CustomerLedger extends Model {
    protected static string $table = 'customer_ledger';
    protected static string $primaryKey = 'id';

    public static function tenantId(): int {
        return Auth::tenantId();
    }

    /**
     * Get ledger entries for a customer with pagination
     */
    public static function getCustomerLedger(int $customerId, int $page = 1, array $filters = []): array {
        $tid = static::tenantId();
        $where = "cl.tenant_id = :tid AND cl.customer_id = :customer_id";
        $params = [':tid' => $tid, ':customer_id' => $customerId];

        if (!empty($filters['date_from'])) {
            $where .= " AND cl.transaction_date >= :date_from";
            $params[':date_from'] = $filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $where .= " AND cl.transaction_date <= :date_to";
            $params[':date_to'] = $filters['date_to'];
        }

        if (!empty($filters['transaction_type'])) {
            $where .= " AND cl.transaction_type = :transaction_type";
            $params[':transaction_type'] = $filters['transaction_type'];
        }

        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $off = ($page - 1) * $pp;

        // Get total count
        $cnt = db()->prepare("SELECT COUNT(*) FROM customer_ledger cl WHERE {$where}");
        $cnt->execute($params);
        $total = (int)$cnt->fetchColumn();

        // Get paginated data
        $sql = "SELECT cl.*, c.name as customer_name, u.name as user_name
                FROM customer_ledger cl
                LEFT JOIN customers c ON c.id = cl.customer_id
                LEFT JOIN users u ON u.id = cl.user_id
                WHERE {$where}
                ORDER BY cl.transaction_date DESC, cl.id DESC
                LIMIT {$pp} OFFSET {$off}";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $entries = $stmt->fetchAll();

        // Calculate opening balance
        $openingBalance = static::getOpeningBalance($customerId, $filters['date_from'] ?? null);

        return [
            'data' => $entries,
            'total' => $total,
            'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp),
            'page' => $page,
            'opening_balance' => $openingBalance
        ];
    }

    /**
     * Get opening balance before a specific date
     */
    public static function getOpeningBalance(int $customerId, ?string $dateBefore = null): float {
        $tid = static::tenantId();
        $sql = "SELECT COALESCE(SUM(debit - credit), 0) as balance
                FROM customer_ledger
                WHERE tenant_id = :tid AND customer_id = :customer_id";
        $params = [':tid' => $tid, ':customer_id' => $customerId];

        if ($dateBefore) {
            $sql .= " AND transaction_date < :date";
            $params[':date'] = $dateBefore;
        }

        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        return (float)$stmt->fetchColumn();
    }

    /**
     * Get current balance for a customer
     */
    public static function getCurrentBalance(int $customerId): float {
        return static::getOpeningBalance($customerId);
    }

    /**
     * Add a ledger entry
     */
    public static function addEntry(array $data): bool {
        $tid = static::tenantId();
        
        // Get current balance before this transaction
        $currentBalance = static::getOpeningBalance($data['customer_id']);
        
        // Calculate new balance
        $debit = $data['debit'] ?? 0;
        $credit = $data['credit'] ?? 0;
        $newBalance = $currentBalance + $debit - $credit;

        $sql = "INSERT INTO customer_ledger 
                (tenant_id, customer_id, transaction_type, reference_id, reference_no, 
                 debit, credit, balance, description, transaction_date, user_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = db()->prepare($sql);
        return $stmt->execute([
            $tid,
            $data['customer_id'],
            $data['transaction_type'],
            $data['reference_id'] ?? null,
            $data['reference_no'] ?? null,
            $debit,
            $credit,
            $newBalance,
            $data['description'] ?? null,
            $data['transaction_date'] ?? date('Y-m-d'),
            Auth::userId()
        ]);
    }

    /**
     * Get all customers with due balances
     */
    public static function getCustomersWithDueBalance(): array {
        $tid = static::tenantId();
        $sql = "SELECT c.id, c.name, c.phone, c.email,
                COALESCE(SUM(cl.debit - cl.credit), 0) as balance
                FROM customers c
                LEFT JOIN customer_ledger cl ON cl.customer_id = c.id AND cl.tenant_id = c.tenant_id
                WHERE c.tenant_id = :tid
                GROUP BY c.id
                HAVING balance > 0
                ORDER BY balance DESC";
        
        $stmt = db()->prepare($sql);
        $stmt->execute([':tid' => $tid]);
        return $stmt->fetchAll();
    }

    /**
     * Get customer ledger summary
     */
    public static function getCustomerSummary(int $customerId): array {
        $tid = static::tenantId();
        $sql = "SELECT 
                COUNT(*) as total_transactions,
                SUM(debit) as total_debit,
                SUM(credit) as total_credit,
                COALESCE(SUM(debit - credit), 0) as current_balance
                FROM customer_ledger
                WHERE tenant_id = :tid AND customer_id = :customer_id";
        
        $stmt = db()->prepare($sql);
        $stmt->execute([':tid' => $tid, ':customer_id' => $customerId]);
        return $stmt->fetch();
    }
}
