<?php
class Bill {
    /**
     * Get all bills with customer info (untuk admin)
     */
    public static function getAll($status = '', $branchId = null, $limit = 200) {
        $db = db();
        
        $sql = "
            SELECT 
                b.*,
                c.name as customer_name,
                c.customer_number,
                c.phone,
                c.pppoe_username,
                br.name as branch_name
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
            LEFT JOIN branches br ON c.branch_id = br.id
        ";
        
        $where = [];
        $params = [];
        
        if ($status) {
            $where[] = "b.status = ?";
            $params[] = $status;
        }
        
        if ($branchId !== null && $branchId !== '') {
            $where[] = "c.branch_id = ?";
            $params[] = (int)$branchId;
        }
        
        if (!empty($where)) {
            $sql .= " WHERE " . implode(' AND ', $where);
        }
        
        $sql .= " ORDER BY b.due_date DESC, b.created_at DESC LIMIT ?";
        $params[] = (int)$limit;
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $bills = $stmt->fetchAll();
        
        foreach ($bills as &$bill) {
            $timestamp = strtotime($bill['due_date']);
            $bill['period_month'] = (int)date('m', $timestamp);
            $bill['period_year'] = (int)date('Y', $timestamp);
            $bill['branch_name'] = $bill['branch_name'] ?? '-';
        }
        
        return $bills;
    }
    
    public static function all($customerId = null, $status = '', $limit = 100, $branchId = null) {
        return self::getAll($status, $branchId, $limit);
    }
    
    /**
     * Generate tagihan baru untuk customer
     */
    public static function generate($customerId, $amount = null, $dueDate = null) {
        try {
            $db = db();
            
            $stmt = $db->prepare("
                SELECT c.*, p.price as package_price, p.name as package_name
                FROM customers c
                LEFT JOIN packages p ON c.package_id = p.id
                WHERE c.id = ?
            ");
            $stmt->execute([$customerId]);
            $customer = $stmt->fetch();
            
            if (!$customer) {
                return ['success' => false, 'error' => 'Customer tidak ditemukan'];
            }
            
            $stmt = $db->prepare("
                SELECT id FROM bills 
                WHERE customer_id = ? AND status IN ('unpaid', 'overdue')
            ");
            $stmt->execute([$customerId]);
            if ($stmt->fetch()) {
                return ['success' => false, 'error' => 'Customer sudah memiliki tagihan yang belum dibayar'];
            }
            
            $amount = $amount ?? $customer['package_price'] ?? 0;
            
            $yearMonth = date('Ym');
            $stmt = $db->prepare("
                SELECT COUNT(*) as count FROM bills 
                WHERE bill_number LIKE ?
            ");
            $stmt->execute(["BILL-$yearMonth-%"]);
            $count = $stmt->fetch()['count'] + 1;
            $billNumber = sprintf("BILL-%s-%03d", $yearMonth, $count);
            
            $dueDay = $customer['due_day'] ?? 28;
            if (!$dueDate) {
                $currentMonth = date('Y-m');
                $dueDate = sprintf('%s-%02d', $currentMonth, min($dueDay, 28));
                
                if (strtotime($dueDate) < strtotime('today')) {
                    $nextMonth = date('Y-m', strtotime('next month'));
                    $dueDate = sprintf('%s-%02d', $nextMonth, min($dueDay, 28));
                }
            }
            
            $timestamp = strtotime($dueDate);
            $periodMonth = (int)date('m', $timestamp);
            $periodYear = (int)date('Y', $timestamp);
            
            $stmt = $db->prepare("
                INSERT INTO bills (customer_id, branch_id, bill_number, amount, due_date, period_month, period_year, status)
                VALUES (?, ?, ?, ?, ?, ?, ?, 'unpaid')
            ");
            $stmt->execute([
                $customerId,
                $customer['branch_id'] ?? null,
                $billNumber,
                $amount,
                $dueDate,
                $periodMonth,
                $periodYear
            ]);
            
            $billId = $db->lastInsertId();
            
            return [
                'success' => true,
                'id' => $billId,
                'bill_number' => $billNumber,
                'amount' => $amount,
                'due_date' => $dueDate
            ];
            
        } catch (Exception $e) {
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    /**
     * ⭐ DIPERBAIKI: Proses pembayaran tagihan
     * TAMBAH PARAMETER KE-4: $paymentType ('cash' atau 'link')
     */
    public static function pay($billId, $paymentMethod = 'Tunai', $category = 'Pelunasan Tagihan', $paymentType = 'link') {
        try {
            $db = db();
            
            // ⭐ NORMALISASI: Pastikan payment_type hanya 'cash' atau 'link'
            $paymentType = in_array($paymentType, ['cash', 'link']) ? $paymentType : 'link';
            
            error_log("Bill::pay() called - billId=$billId, method=$paymentMethod, category=$category, type=$paymentType");
            
            // Ambil data bill + customer
            $stmt = $db->prepare("
                SELECT b.*, c.branch_id, c.id as customer_id, c.name as customer_name,
                       c.phone, c.customer_number, c.package_id
                FROM bills b
                LEFT JOIN customers c ON b.customer_id = c.id
                WHERE b.id = ?
            ");
            $stmt->execute([$billId]);
            $bill = $stmt->fetch();
            
            if (!$bill) {
                return ['success' => false, 'error' => 'Tagihan tidak ditemukan'];
            }
            
            if ($bill['status'] === 'paid') {
                return ['success' => false, 'error' => 'Tagihan sudah dibayar'];
            }
            
            $db->beginTransaction();
            
            // 1. Update bill status
            $hasCategoryInBills = self::columnExists('bills', 'category');
            
            if ($hasCategoryInBills) {
                $stmt = $db->prepare("
                    UPDATE bills 
                    SET status = 'paid', 
                        paid_at = NOW(),
                        payment_method = ?,
                        category = ?,
                        updated_at = NOW()
                    WHERE id = ?
                ");
                $stmt->execute([$paymentMethod, $category, $billId]);
            } else {
                $stmt = $db->prepare("
                    UPDATE bills 
                    SET status = 'paid', 
                        paid_at = NOW(),
                        payment_method = ?,
                        updated_at = NOW()
                    WHERE id = ?
                ");
                $stmt->execute([$paymentMethod, $billId]);
            }
            
            // 2. Hitung masa aktif
            $activeUntil = date('Y-m-d 23:59:59', strtotime('+1 month', strtotime($bill['due_date'])));
            
            // 3. Update customer status
            $stmt = $db->prepare("
                UPDATE customers 
                SET status = 'active', 
                    active_until = ?,
                    updated_at = NOW() 
                WHERE id = ?
            ");
            $stmt->execute([$activeUntil, $bill['customer_id']]);
            
            // 4. ⭐ CATAT KE branch_transactions DENGAN payment_type YANG BENAR
            if (!empty($bill['branch_id'])) {
                // ⭐ LOGIKA BARU: Cash = released langsung, Link = pending H+5
                $trxStatus = ($paymentType === 'cash') ? 'released' : 'pending';
                $pendingUntil = ($paymentType === 'cash') ? null : date('Y-m-d H:i:s', strtotime('+5 days'));
                $releasedAt = ($paymentType === 'cash') ? date('Y-m-d H:i:s') : null;
                
                // Cek kolom yang ada
                $hasPaymentType = self::columnExists('branch_transactions', 'payment_type');
                $hasCategory = self::columnExists('branch_transactions', 'category');
                
                // Build query dinamis
                $columns = ['branch_id', 'bill_id', 'customer_id', 'bill_number', 
                            'customer_name', 'amount', 'payment_method', 'status', 'created_at'];
                $values = [$bill['branch_id'], $bill['id'], $bill['customer_id'], $bill['bill_number'],
                           $bill['customer_name'], $bill['amount'], $paymentMethod, $trxStatus, date('Y-m-d H:i:s')];
                
                if ($hasCategory) {
                    array_splice($columns, 7, 0, ['category']);
                    array_splice($values, 7, 0, [$category]);
                }
                
                if ($hasPaymentType) {
                    array_splice($columns, 7, 0, ['payment_type']);
                    array_splice($values, 7, 0, [$paymentType]);
                }
                
                if ($paymentType === 'cash') {
    // Cash: set released_at dan pending_until = created_at
    $columns[] = 'released_at';
    $values[] = $releasedAt;
    $placeholders[] = '?';
    
    $columns[] = 'pending_until';
    $values[] = date('Y-m-d H:i:s'); // Sama dengan created_at
    $placeholders[] = '?';
} else {
    // Link: hanya set pending_until
    $columns[] = 'pending_until';
    $values[] = $pendingUntil;
    $placeholders[] = '?';
}
                
                $placeholders = implode(', ', array_fill(0, count($columns), '?'));
                $sql = "INSERT INTO branch_transactions (" . implode(', ', $columns) . ") VALUES ($placeholders)";
                
                $stmt = $db->prepare($sql);
                $stmt->execute($values);
                
                // 5. ⭐ UPDATE branch_balances
                if ($paymentType === 'cash') {
                    // CASH: Langsung masuk available_balance
                    $stmt = $db->prepare("
                        INSERT INTO branch_balances 
                        (branch_id, pending_balance, available_balance, total_withdrawn, total_income)
                        VALUES (?, 0, ?, 0, ?)
                        ON DUPLICATE KEY UPDATE 
                            available_balance = available_balance + VALUES(available_balance),
                            total_income = total_income + VALUES(total_income),
                            last_updated_at = NOW()
                    ");
                    $stmt->execute([$bill['branch_id'], $bill['amount'], $bill['amount']]);
                } else {
                    // LINK: Masuk pending_balance dulu
                    $stmt = $db->prepare("
                        INSERT INTO branch_balances 
                        (branch_id, pending_balance, available_balance, total_withdrawn, total_income)
                        VALUES (?, ?, 0, 0, ?)
                        ON DUPLICATE KEY UPDATE 
                            pending_balance = pending_balance + VALUES(pending_balance),
                            total_income = total_income + VALUES(total_income),
                            last_updated_at = NOW()
                    ");
                    $stmt->execute([$bill['branch_id'], $bill['amount'], $bill['amount']]);
                }
            }
            
            $db->commit();
            
            // Ambil data paket
            $packageName = '-';
            if (!empty($bill['package_id'])) {
                $stmt = $db->prepare("SELECT name FROM packages WHERE id = ?");
                $stmt->execute([$bill['package_id']]);
                $pkg = $stmt->fetch();
                if ($pkg) $packageName = $pkg['name'];
            }
            
            error_log("Bill::pay() SUCCESS - Bill #$billId, type=$paymentType, status=$trxStatus");
            
            return [
                'success' => true,
                'bill_id' => $bill['id'],
                'bill_number' => $bill['bill_number'],
                'customer_id' => $bill['customer_id'],
                'customer_name' => $bill['customer_name'],
                'customer_number' => $bill['customer_number'] ?? '-',
                'phone' => $bill['phone'] ?? '',
                'package_name' => $packageName,
                'amount' => $bill['amount'],
                'payment_method' => $paymentMethod,
                'payment_type' => $paymentType,
                'category' => $category,
                'active_until' => $activeUntil,
                'message' => "Pembayaran berhasil. Masa aktif sampai " . date('d/m/Y', strtotime($activeUntil))
            ];
            
        } catch (Exception $e) {
            if (isset($db) && $db->inTransaction()) {
                $db->rollBack();
            }
            error_log("Bill::pay() ERROR: " . $e->getMessage());
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    /**
     * Helper: Cek apakah kolom ada di tabel
     */
    private static function columnExists($table, $column) {
        try {
            $db = db();
            $stmt = $db->prepare("
                SELECT COUNT(*) 
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = ? 
                AND COLUMN_NAME = ?
            ");
            $stmt->execute([$table, $column]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }
    
    /**
     * Generate tagihan bulan depan
     */
    public static function generateNextMonth($customerId) {
        try {
            $db = db();
            
            $stmt = $db->prepare("
                SELECT c.*, p.price as package_price, p.name as package_name
                FROM customers c
                LEFT JOIN packages p ON c.package_id = p.id
                WHERE c.id = ?
            ");
            $stmt->execute([$customerId]);
            $customer = $stmt->fetch();
            
            if (!$customer) {
                return ['success' => false, 'error' => 'Customer tidak ditemukan'];
            }
            
            $nextMonth = date('Y-m', strtotime('+1 month'));
            $stmt = $db->prepare("
                SELECT id FROM bills 
                WHERE customer_id = ? AND DATE_FORMAT(due_date, '%Y-%m') = ?
            ");
            $stmt->execute([$customerId, $nextMonth]);
            if ($stmt->fetch()) {
                return ['success' => false, 'error' => 'Tagihan bulan depan sudah ada'];
            }
            
            $stmt = $db->prepare("
                SELECT due_date FROM bills 
                WHERE customer_id = ? 
                ORDER BY due_date DESC LIMIT 1
            ");
            $stmt->execute([$customerId]);
            $lastBill = $stmt->fetch();
            
            if ($lastBill) {
                $dueDate = date('Y-m-d', strtotime('+1 month', strtotime($lastBill['due_date'])));
            } else {
                $dueDay = $customer['due_day'] ?? 28;
                $dueDate = date('Y-m-', strtotime('+1 month')) . sprintf('%02d', min($dueDay, 28));
            }
            
            $yearMonth = date('Ym', strtotime($dueDate));
            $stmt = $db->prepare("
                SELECT COUNT(*) as count FROM bills 
                WHERE bill_number LIKE ?
            ");
            $stmt->execute(["BILL-$yearMonth-%"]);
            $count = $stmt->fetch()['count'] + 1;
            $billNumber = sprintf("BILL-%s-%03d", $yearMonth, $count);
            
            $amount = $customer['package_price'] ?? 0;
            $timestamp = strtotime($dueDate);
            $periodMonth = (int)date('m', $timestamp);
            $periodYear = (int)date('Y', $timestamp);
            
            $stmt = $db->prepare("
                INSERT INTO bills 
                (customer_id, branch_id, bill_number, amount, due_date, period_month, period_year, status, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, 'unpaid', NOW())
            ");
            $stmt->execute([
                $customerId,
                $customer['branch_id'] ?? null,
                $billNumber,
                $amount,
                $dueDate,
                $periodMonth,
                $periodYear
            ]);
            
            $billId = $db->lastInsertId();
            
            return [
                'success' => true,
                'id' => $billId,
                'bill_number' => $billNumber,
                'amount' => $amount,
                'due_date' => $dueDate,
                'message' => "Tagihan bulan depan digenerate: $billNumber"
            ];
            
        } catch (Exception $e) {
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    public static function getLatestUnpaid($customerId) {
        $db = db();
        $stmt = $db->prepare("
            SELECT * FROM bills 
            WHERE customer_id = ? AND status IN ('unpaid', 'overdue')
            ORDER BY due_date ASC
            LIMIT 1
        ");
        $stmt->execute([$customerId]);
        return $stmt->fetch();
    }
    
    public static function getByCustomer($customerId, $limit = 10) {
        $db = db();
        $stmt = $db->prepare("
            SELECT * FROM bills 
            WHERE customer_id = ?
            ORDER BY created_at DESC
            LIMIT ?
        ");
        $stmt->execute([$customerId, (int)$limit]);
        return $stmt->fetchAll();
    }
    
    public static function find($id) {
        $db = db();
        $stmt = $db->prepare("
            SELECT b.*, c.name as customer_name, c.phone, c.pppoe_username, c.customer_number, c.branch_id
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
            WHERE b.id = ?
        ");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
    
    public static function markOverdue() {
        $db = db();
        $stmt = $db->prepare("
            UPDATE bills 
            SET status = 'overdue', updated_at = NOW()
            WHERE status = 'unpaid' AND due_date < CURDATE()
        ");
        $stmt->execute();
        return $stmt->rowCount();
    }
    
    public static function processOverdue($branchId = null) {
        $db = db();
        $result = ['marked' => 0, 'suspended' => 0, 'errors' => []];
        
        try {
            $sql = "
                SELECT b.*, c.pppoe_username, c.name as customer_name, c.branch_id, c.id as customer_id
                FROM bills b
                LEFT JOIN customers c ON b.customer_id = c.id
                WHERE b.status = 'unpaid' AND b.due_date < CURDATE()
            ";
            
            $params = [];
            
            if ($branchId !== null && $branchId !== '') {
                $sql .= " AND c.branch_id = ?";
                $params[] = (int)$branchId;
            }
            
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $overdueBills = $stmt->fetchAll();
            
            if (empty($overdueBills)) {
                return $result;
            }
            
            $updateSql = "
                UPDATE bills b
                LEFT JOIN customers c ON b.customer_id = c.id
                SET b.status = 'overdue', b.updated_at = NOW()
                WHERE b.status = 'unpaid' AND b.due_date < CURDATE()
            ";
            
            $updateParams = [];
            if ($branchId !== null && $branchId !== '') {
                $updateSql .= " AND c.branch_id = ?";
                $updateParams[] = (int)$branchId;
            }
            
            $stmt = $db->prepare($updateSql);
            $stmt->execute($updateParams);
            $result['marked'] = $stmt->rowCount();
            
            foreach ($overdueBills as $bill) {
                try {
                    $stmt = $db->prepare("
                        UPDATE customers SET status = 'suspended', updated_at = NOW()
                        WHERE id = ? AND status != 'suspended'
                    ");
                    $stmt->execute([$bill['customer_id']]);
                    $result['suspended']++;
                    
                    if (!empty($bill['pppoe_username'])) {
                        try {
                            require_once __DIR__ . '/MikroTik.php';
                            $mt = new MikroTik($bill['branch_id']);
                            $mt->suspendCustomer($bill['pppoe_username']);
                        } catch (\Throwable $e) {}
                    }
                } catch (\Throwable $e) {
                    $result['errors'][] = "Error suspending customer #{$bill['customer_id']}: " . $e->getMessage();
                }
            }
            
        } catch (Exception $e) {
            $result['errors'][] = $e->getMessage();
        }
        
        return $result;
    }
    
    public static function getStats($branchId = null) {
        $db = db();
        
        $sql = "
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN b.status = 'unpaid' THEN 1 ELSE 0 END) as unpaid_count,
                SUM(CASE WHEN b.status = 'paid' THEN 1 ELSE 0 END) as paid_count,
                SUM(CASE WHEN b.status = 'overdue' THEN 1 ELSE 0 END) as overdue_count,
                SUM(CASE WHEN b.status IN ('unpaid', 'overdue') THEN b.amount ELSE 0 END) as unpaid_amount,
                SUM(CASE WHEN b.status = 'paid' THEN b.amount ELSE 0 END) as paid_amount
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
        ";
        
        $params = [];
        
        if ($branchId !== null && $branchId !== '') {
            $sql .= " WHERE c.branch_id = ?";
            $params[] = (int)$branchId;
        }
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        
        $stats = $stmt->fetch();
        
        return [
            'total' => (int)($stats['total'] ?? 0),
            'unpaid_count' => (int)($stats['unpaid_count'] ?? 0),
            'paid_count' => (int)($stats['paid_count'] ?? 0),
            'overdue_count' => (int)($stats['overdue_count'] ?? 0),
            'unpaid_amount' => (float)($stats['unpaid_amount'] ?? 0),
            'paid_amount' => (float)($stats['paid_amount'] ?? 0),
        ];
    }
    
    public static function delete($id) {
    try {
        $db = db();
        
        $stmt = $db->prepare("
            SELECT b.*, c.active_until, c.pppoe_username, c.branch_id, c.status as customer_status
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
            WHERE b.id = ?
        ");
        $stmt->execute([$id]);
        $bill = $stmt->fetch();
        
        if (!$bill) {
            return ['success' => false, 'error' => 'Tagihan tidak ditemukan'];
        }
        
        $db->beginTransaction();
        
        $stmt = $db->prepare("DELETE FROM bills WHERE id = ?");
        $stmt->execute([$id]);
        
        $stmt = $db->prepare("
            SELECT COUNT(*) as count FROM bills 
            WHERE customer_id = ? AND status IN ('unpaid', 'overdue')
        ");
        $stmt->execute([$bill['customer_id']]);
        $count = $stmt->fetch()['count'];
        
        if ($count == 0) {
            $stmt = $db->prepare("UPDATE customers SET status = 'active', updated_at = NOW() WHERE id = ?");
            $stmt->execute([$bill['customer_id']]);
            
            // ⭐ PERBAIKAN: Jika tagihan yang dihapus sudah PAID, kurangi masa aktif 30 hari
            if ($bill['status'] === 'paid' && !empty($bill['active_until'])) {
                $newActiveUntil = date('Y-m-d 23:59:59', strtotime('-30 days', strtotime($bill['active_until'])));
                $stmt = $db->prepare("UPDATE customers SET active_until = ?, updated_at = NOW() WHERE id = ?");
                $stmt->execute([$newActiveUntil, $bill['customer_id']]);
                
                error_log("Bill::delete() - Tagihan PAID dihapus, masa aktif dikurangi 30 hari. Customer ID: {$bill['customer_id']}");
            }
            
            if (!empty($bill['pppoe_username']) && $bill['customer_status'] === 'suspended') {
                try {
                    require_once __DIR__ . '/MikroTik.php';
                    $mt = new MikroTik($bill['branch_id']);
                    $mt->restoreCustomer($bill['pppoe_username']);
                } catch (\Throwable $e) {
                    error_log("MikroTik restore error: " . $e->getMessage());
                }
            }
        }
        
        $db->commit();
        
        return ['success' => true];
    } catch (Exception $e) {
        if (isset($db) && $db->inTransaction()) {
            $db->rollBack();
        }
        return ['success' => false, 'error' => $e->getMessage()];
    }
}
    
    public static function hasAccess($billId, $userBranchId = null) {
        if ($userBranchId === null) {
            return true;
        }
        
        $db = db();
        $stmt = $db->prepare("
            SELECT c.branch_id 
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
            WHERE b.id = ?
        ");
        $stmt->execute([$billId]);
        $bill = $stmt->fetch();
        
        if (!$bill) {
            return false;
        }
        
        return (int)$bill['branch_id'] === (int)$userBranchId;
    }
    
    public static function getTotalUnpaidAmount($branchId = null) {
        $db = db();
        
        $sql = "
            SELECT COALESCE(SUM(b.amount), 0) as total
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
            WHERE b.status IN ('unpaid', 'overdue')
        ";
        
        $params = [];
        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND c.branch_id = ?";
            $params[] = (int)$branchId;
        }
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        
        return (float)($result['total'] ?? 0);
    }
    
    public static function getCountByStatus($branchId = null) {
        $db = db();
        
        $sql = "
            SELECT 
                b.status,
                COUNT(*) as count
            FROM bills b
            LEFT JOIN customers c ON b.customer_id = c.id
        ";
        
        $params = [];
        if ($branchId !== null && $branchId !== '') {
            $sql .= " WHERE c.branch_id = ?";
            $params[] = (int)$branchId;
        }
        
        $sql .= " GROUP BY b.status";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        
        $result = [
            'unpaid' => 0,
            'paid' => 0,
            'overdue' => 0,
        ];
        
        foreach ($rows as $row) {
            if (isset($result[$row['status']])) {
                $result[$row['status']] = (int)$row['count'];
            }
        }
        
        return $result;
    }
}