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

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

    /**
     * Get ledger entries for a supplier with pagination
     */
    public static function getSupplierLedger(int $supplierId, int $page = 1, array $filters = []): array {
        $tid = static::tenantId();
        $where = "sl.tenant_id = :tid AND sl.supplier_id = :supplier_id";
        $params = [':tid' => $tid, ':supplier_id' => $supplierId];

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

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

        if (!empty($filters['transaction_type'])) {
            $where .= " AND sl.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 supplier_ledger sl WHERE {$where}");
        $cnt->execute($params);
        $total = (int)$cnt->fetchColumn();

        // Get paginated data
        $sql = "SELECT sl.*, s.name as supplier_name, u.name as user_name
                FROM supplier_ledger sl
                LEFT JOIN suppliers s ON s.id = sl.supplier_id
                LEFT JOIN users u ON u.id = sl.user_id
                WHERE {$where}
                ORDER BY sl.transaction_date DESC, sl.id DESC
                LIMIT {$pp} OFFSET {$off}";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $entries = $stmt->fetchAll();

        // Calculate opening balance
        $openingBalance = static::getOpeningBalance($supplierId, $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 $supplierId, ?string $dateBefore = null): float {
        $tid = static::tenantId();
        $sql = "SELECT COALESCE(SUM(debit - credit), 0) as balance
                FROM supplier_ledger
                WHERE tenant_id = :tid AND supplier_id = :supplier_id";
        $params = [':tid' => $tid, ':supplier_id' => $supplierId];

        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 supplier
     */
    public static function getCurrentBalance(int $supplierId): float {
        return static::getOpeningBalance($supplierId);
    }

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

        $sql = "INSERT INTO supplier_ledger 
                (tenant_id, supplier_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['supplier_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 suppliers with due balances
     */
    public static function getSuppliersWithDueBalance(): array {
        $tid = static::tenantId();
        $sql = "SELECT s.id, s.name, s.phone, s.email,
                COALESCE(SUM(sl.debit - sl.credit), 0) as balance
                FROM suppliers s
                LEFT JOIN supplier_ledger sl ON sl.supplier_id = s.id AND sl.tenant_id = s.tenant_id
                WHERE s.tenant_id = :tid
                GROUP BY s.id
                HAVING balance > 0
                ORDER BY balance DESC";
        
        $stmt = db()->prepare($sql);
        $stmt->execute([':tid' => $tid]);
        return $stmt->fetchAll();
    }

    /**
     * Get supplier ledger summary
     */
    public static function getSupplierSummary(int $supplierId): 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 supplier_ledger
                WHERE tenant_id = :tid AND supplier_id = :supplier_id";
        
        $stmt = db()->prepare($sql);
        $stmt->execute([':tid' => $tid, ':supplier_id' => $supplierId]);
        return $stmt->fetch();
    }
}
