<?php
require_once __DIR__ . '/../includes/db.php';

class Customer {
    /**
     * ⭐ UPDATED: Tambah pelanggan + sync ke MikroTik + auto-generate tagihan pertama
     * 
     * Fitur baru:
     * - Tanggal instalasi (installation_date)
     * - Auto-calculate jatuh tempo = tanggal instalasi + 1 bulan
     * - Auto-set active_until = tanggal instalasi + 1 bulan
     * - Auto-generate tagihan pertama dengan jatuh tempo tersebut
     */
    public static function create($data) {
        try {
            $db = db();
            
            // ====================================================================
            // GENERATE CUSTOMER NUMBER BERDASARKAN CABANG
            // Pusat (branch_id = NULL/0) → LFN-00001
            // Cabang (branch_id = X)     → BR{X}-00001
            // ====================================================================
            $branchId = !empty($data['branch_id']) ? (int)$data['branch_id'] : null;
            
            if ($branchId === null || $branchId === 0) {
                $prefix = 'LFN';
                $branchFilter = '(branch_id IS NULL OR branch_id = 0)';
                $params = [];
            } else {
                $prefix = 'BR' . $branchId;
                $branchFilter = 'branch_id = ?';
                $params = [$branchId];
            }
            
            $sql = "SELECT customer_number FROM customers 
                    WHERE customer_number LIKE ? AND $branchFilter
                    ORDER BY customer_number DESC LIMIT 1";
            array_unshift($params, $prefix . '-%');
            
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $lastCustomer = $stmt->fetch();
            
            if ($lastCustomer) {
                $lastNumber = (int)substr($lastCustomer['customer_number'], strlen($prefix) + 1);
                $newNumber = $lastNumber + 1;
            } else {
                $newNumber = 1;
            }
            
            $customerNumber = sprintf('%s-%05d', $prefix, $newNumber);
            
            // ====================================================================
            // ⭐ HANDLE TANGGAL INSTALASI & AUTO-CALCULATE
            // ====================================================================
            $installationDate = $data['installation_date'] ?? date('Y-m-d');
            
            // Validasi format tanggal
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $installationDate)) {
                return ['success' => false, 'error' => 'Format tanggal instalasi tidak valid'];
            }
            
            // ⭐ due_day = tanggal dari installation_date (1-28)
            $dueDay = (int)date('j', strtotime($installationDate));
            $dueDay = min($dueDay, 28); // Max 28
            
            // ⭐ active_until = tanggal instalasi + 1 bulan (jam 23:59:59)
            $activeUntil = date('Y-m-d 23:59:59', strtotime('+1 month', strtotime($installationDate)));
            
            // ====================================================================
            // INSERT DATA PELANGGAN (dengan kolom baru)
            // ====================================================================
            $db->beginTransaction();
            
            $stmt = $db->prepare("
                INSERT INTO customers (
                    customer_number, branch_id, name, phone, email, address,
                    pppoe_username, pppoe_password, pppoe_service, pppoe_profile,
                    package_id, ip_address, due_day, installation_date, active_until,
                    status, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', NOW())
            ");
            
            $stmt->execute([
                $customerNumber,
                $branchId,
                $data['name'],
                $data['phone'],
                $data['email'] ?? null,
                $data['address'] ?? null,
                $data['pppoe_username'] ?? null,
                $data['pppoe_password'] ?? null,
                $data['pppoe_service'] ?? 'pppoe',
                $data['pppoe_profile'] ?? 'default',
                $data['package_id'],
                $data['ip_address'] ?? null,
                $dueDay,
                $installationDate,
                $activeUntil
            ]);
            
            $customerId = $db->lastInsertId();
            
            // ====================================================================
            // SYNC KE MIKROTIK (jika ada data PPPoE)
            // ====================================================================
            $synced = false;
            if (!empty($data['pppoe_username']) && !empty($data['pppoe_password'])) {
                try {
                    require_once __DIR__ . '/MikroTik.php';
                    $mt = new MikroTik($branchId);
                    
                    $useExisting = !empty($data['use_existing_secret']);
                    
                    if ($useExisting) {
                        $result = $mt->enableSecret($data['pppoe_username']);
                        $synced = $result['success'] ?? false;
                    } else {
                        $comment = 'Customer: ' . $data['name'] . ' (' . $customerNumber . ')';
                        $result = $mt->addSecret(
                            $data['pppoe_username'],
                            $data['pppoe_password'],
                            $data['pppoe_profile'] ?? 'default',
                            $data['pppoe_service'] ?? 'pppoe',
                            $comment
                        );
                        $synced = $result['success'] ?? false;
                    }
                } catch (\Throwable $e) {
                    // Ignore MikroTik error, customer tetap tersimpan
                }
            }
            
            // ====================================================================
            // ⭐ AUTO-GENERATE TAGIHAN PERTAMA
            // Due date = tanggal instalasi + 1 bulan
            // ====================================================================
            $firstDueDate = date('Y-m-d', strtotime('+1 month', strtotime($installationDate)));
            $yearMonth = date('Ym', strtotime($firstDueDate));
            
            // Generate bill number
            $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);
            
            // Ambil harga paket
            $stmt = $db->prepare("SELECT price FROM packages WHERE id = ?");
            $stmt->execute([$data['package_id']]);
            $package = $stmt->fetch();
            $amount = $package ? (float)$package['price'] : 0;
            
            $periodMonth = (int)date('m', strtotime($firstDueDate));
            $periodYear = (int)date('Y', strtotime($firstDueDate));
            
            $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,
                $branchId,
                $billNumber,
                $amount,
                $firstDueDate,
                $periodMonth,
                $periodYear
            ]);
            
            $db->commit();
            
            return [
                'success' => true,
                'id' => $customerId,
                'customer_number' => $customerNumber,
                'synced' => $synced,
                'bill_number' => $billNumber,
                'first_due_date' => $firstDueDate,
                'active_until' => $activeUntil,
                'message' => "Pelanggan berhasil ditambahkan. Tagihan pertama #$billNumber (Jatuh tempo: " . date('d/m/Y', strtotime($firstDueDate)) . ")"
            ];
            
        } catch (Exception $e) {
            if (isset($db) && $db->inTransaction()) {
                $db->rollBack();
            }
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    /**
     * Update pelanggan
     */
    public static function update($id, $data) {
        $db = db();
        
        // Build dynamic update query
        $fields = [];
        $params = [];
        
        $allowedFields = [
            'name', 'package_id', 'phone', 'email', 'address', 'ip_address',
            'pppoe_username', 'pppoe_password', 'pppoe_service', 'pppoe_profile',
            'due_day', 'installation_date', 'active_until', 'status'
        ];
        
        foreach ($allowedFields as $field) {
            if (array_key_exists($field, $data)) {
                $fields[] = "$field = ?";
                $params[] = $data[$field];
            }
        }
        
        if (empty($fields)) {
            return false;
        }
        
        $params[] = $id;
        $sql = "UPDATE customers SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        
        $stmt = $db->prepare($sql);
        return $stmt->execute($params);
    }

    /**
     * Hapus pelanggan + hapus secret MikroTik
     */
    public static function delete($id) {
        $db = db();
        $cust = self::find($id);
        if (!$cust) return false;

        require_once __DIR__ . '/MikroTik.php';
        $mt = new MikroTik($cust['branch_id'] ?? null);
        $mt->killActiveConnection($cust['pppoe_username']);
        $mt->removeSecret($cust['pppoe_username']);

        return $db->prepare("DELETE FROM customers WHERE id = ?")->execute([$id]);
    }

    /**
     * Suspend pelanggan (telat bayar)
     */
    public static function suspend($id) {
        $db = db();
        $cust = self::find($id);
        if (!$cust) return false;

        require_once __DIR__ . '/MikroTik.php';
        $mt = new MikroTik($cust['branch_id'] ?? null);
        $ok = $mt->suspendCustomer($cust['pppoe_username']);

        if ($ok) {
            $db->prepare("UPDATE customers SET status = 'suspended', internet_status = 'disconnected', updated_at = NOW() WHERE id = ?")
               ->execute([$id]);
            
            // Kirim notifikasi suspend (optional)
            try {
                if (file_exists(__DIR__ . '/Wablas.php')) {
                    require_once __DIR__ . '/Wablas.php';
                    $wablas = new Wablas();
                    $appUrl = require __DIR__ . '/../config/app.php';
                    $wablas->sendSuspensionNotice($cust['phone'], [
                        'name' => $cust['name'],
                        'payment_url' => $appUrl['url'] . '/public/pay.php?id=' . $cust['customer_number']
                    ]);
                }
            } catch (\Throwable $e) {
                // Ignore Wablas error
            }
        }
        return $ok;
    }

    /**
     * Restore pelanggan (setelah bayar)
     */
    public static function restore($id) {
        $db = db();
        $cust = self::find($id);
        if (!$cust) return false;

        require_once __DIR__ . '/MikroTik.php';
        $mt = new MikroTik($cust['branch_id'] ?? null);
        $ok = $mt->restoreCustomer($cust['pppoe_username']);

        if ($ok) {
            $db->prepare("UPDATE customers SET status = 'active', internet_status = 'connected', updated_at = NOW() WHERE id = ?")
               ->execute([$id]);
        }
        return $ok;
    }

    /**
     * Find customer by ID
     */
    public static function find($id) {
        $stmt = db()->prepare("SELECT c.*, p.name as package_name, p.price as package_price, 
                               b.name as branch_name 
                               FROM customers c 
                               LEFT JOIN packages p ON c.package_id = p.id 
                               LEFT JOIN branches b ON c.branch_id = b.id 
                               WHERE c.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }

    /**
     * Find customer by customer_number
     */
    public static function findByNumber($customerNumber) {
        $db = db();
        $stmt = $db->prepare("
            SELECT c.*, b.name as branch_name, p.name as package_name, p.price as package_price
            FROM customers c
            LEFT JOIN branches b ON c.branch_id = b.id
            LEFT JOIN packages p ON c.package_id = p.id
            WHERE c.customer_number = ?
        ");
        $stmt->execute([$customerNumber]);
        return $stmt->fetch();
    }

    /**
     * Get customers by branch
     */
    public static function getByBranch($branchId) {
        $db = db();
        $stmt = $db->prepare("
            SELECT c.*, b.name as branch_name, p.name as package_name
            FROM customers c
            LEFT JOIN branches b ON c.branch_id = b.id
            LEFT JOIN packages p ON c.package_id = p.id
            WHERE c.branch_id = ?
            ORDER BY c.name ASC
        ");
        $stmt->execute([$branchId]);
        return $stmt->fetchAll();
    }

    /**
     * Get all customers (optional filter by branch)
     */
    public static function all($branchId = null) {
        $sql = "SELECT c.*, p.name as package_name, p.price as package_price, b.name as branch_name 
                FROM customers c 
                LEFT JOIN packages p ON c.package_id = p.id 
                LEFT JOIN branches b ON c.branch_id = b.id";
        $params = [];
        if ($branchId) {
            $sql .= " WHERE c.branch_id = ?";
            $params[] = $branchId;
        }
        $sql .= " ORDER BY c.id DESC";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    /**
     * ⭐ NEW: Get customers dengan masa aktif segera habis
     * 
     * @param int $days Number of days threshold (default 7)
     * @param int|null $branchId Filter by branch
     * @return array
     */
    public static function getExpiringSoon($days = 7, $branchId = null) {
        $db = db();
        
        $sql = "
            SELECT c.*, p.name as package_name, b.name as branch_name,
                   DATEDIFF(c.active_until, NOW()) as days_left
            FROM customers c
            LEFT JOIN packages p ON c.package_id = p.id
            LEFT JOIN branches b ON c.branch_id = b.id
            WHERE c.status = 'active' 
              AND c.active_until IS NOT NULL
              AND c.active_until BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL ? DAY)
        ";
        
        $params = [$days];
        
        if ($branchId) {
            $sql .= " AND c.branch_id = ?";
            $params[] = $branchId;
        }
        
        $sql .= " ORDER BY c.active_until ASC";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    /**
     * ⭐ NEW: Get customers yang masa aktifnya sudah expired
     * 
     * @param int|null $branchId Filter by branch
     * @return array
     */
    public static function getExpired($branchId = null) {
        $db = db();
        
        $sql = "
            SELECT c.*, p.name as package_name, b.name as branch_name,
                   DATEDIFF(NOW(), c.active_until) as days_expired
            FROM customers c
            LEFT JOIN packages p ON c.package_id = p.id
            LEFT JOIN branches b ON c.branch_id = b.id
            WHERE c.status = 'active' 
              AND c.active_until IS NOT NULL
              AND c.active_until < NOW()
        ";
        
        $params = [];
        
        if ($branchId) {
            $sql .= " AND c.branch_id = ?";
            $params[] = $branchId;
        }
        
        $sql .= " ORDER BY c.active_until ASC";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
}