seed_dummy.php

<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=bnet_bookkeeping', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// Check if dummy data already exists
$count = $pdo->query("SELECT COUNT(*) FROM customers")->fetchColumn();
if ($count > 5) {
    echo "Dummy data already exists ($count customers), skipping.\n";
    exit;
}

echo "Creating dummy data...\n";

// Ensure at least one employee exists
$empCount = $pdo->query("SELECT COUNT(*) FROM employees")->fetchColumn();
if ($empCount == 0) {
    $pdo->exec("INSERT INTO employees (name, phone, is_active, created_at, updated_at) VALUES ('Budi Karyawan', '0811111111', 1, NOW(), NOW())");
    echo "  Created default employee\n";
}

// Customers data
$customers = [
    ['BN-0001', 'Ahmad Fauzi', 'Jl. Merdeka No. 10, Jakarta', '081234567891', 1, 10, 'active', '2026-01-15'],
    ['BN-0002', 'Siti Rahmawati', 'Jl. Sudirman No. 25, Bandung', '081234567892', 2, 15, 'active', '2026-02-01'],
    ['BN-0003', 'Budi Santoso', 'Jl. Gatot Subroto No. 5, Surabaya', '081234567893', 1, 5, 'active', '2026-01-20'],
    ['BN-0004', 'Dewi Lestari', 'Jl. Ahmad Yani No. 15, Semarang', '081234567894', 3, 20, 'active', '2026-03-01'],
    ['BN-0005', 'Eko Prasetyo', 'Jl. Diponegoro No. 8, Yogyakarta', '081234567895', 2, 12, 'active', '2026-02-10'],
    ['BN-0006', 'Fitri Handayani', 'Jl. Pahlawan No. 12, Malang', '081234567896', 4, 8, 'active', '2026-01-05'],
    ['BN-0007', 'Gunawan Wibowo', 'Jl. Sisingamangaraja No. 3, Medan', '081234567897', 1, 25, 'active', '2026-03-15'],
    ['BN-0008', 'Hesti Purnama', 'Jl. Imam Bonjol No. 20, Padang', '081234567898', 3, 18, 'active', '2026-02-20'],
    ['BN-0009', 'Irfan Maulana', 'Jl. Teuku Umar No. 7, Aceh', '081234567899', 2, 3, 'active', '2026-04-01'],
    ['BN-0010', 'Jeni Anggraini', 'Jl. WR Supratman No. 18, Makassar', '081234567800', 4, 22, 'active', '2026-01-25'],
    ['BN-0011', 'Kurniawan Hidayat', 'Jl. Cendrawasih No. 2, Bali', '081234567801', 1, 14, 'active', '2026-03-01'],
    ['BN-0012', 'Lilis Suryani', 'Jl. Kenanga No. 9, Lombok', '081234567802', 3, 7, 'isolir', '2026-02-05'],
    ['BN-0013', 'Muhammad Rizky', 'Jl. Mawar No. 14, Palembang', '081234567803', 2, 28, 'active', '2026-01-10'],
    ['BN-0014', 'Nina Kusuma', 'Jl. Melati No. 6, Manado', '081234567804', 4, 16, 'deactivated', '2026-02-15'],
    ['BN-0015', 'Oscar Pramono', 'Jl. Anggrek No. 11, Pontianak', '081234567805', 1, 9, 'active', '2026-03-20'],
];

$now = new DateTime('2026-07-11');
$stmt = $pdo->prepare("INSERT INTO customers (customer_id, name, address, phone, package_id, billing_date, status, installation_date, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())");

foreach ($customers as $c) {
    $stmt->execute($c);
    echo "  Customer: {$c[1]}\n";
}

// Get all active customer IDs with their package prices and billing dates
$activeStmt = $pdo->query("SELECT c.id, c.billing_date, p.price FROM customers c JOIN packages p ON c.package_id = p.id WHERE c.status = 'active'");
$activeCustomers = $activeStmt->fetchAll(PDO::FETCH_ASSOC);

$paymentMethods = [1, 2, 3]; // cash_office, cash_employee, bank_transfer

echo "\nGenerating bills and payments...\n";

// Generate bills for last 3 months (May, June, July 2026)
$billInsert = $pdo->prepare("INSERT INTO monthly_bills (customer_id, bill_month, amount, due_date, status, paid_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())");
$paymentInsert = $pdo->prepare("INSERT INTO payments (customer_id, monthly_bill_id, amount, payment_method_id, employee_id, payment_date, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())");

$billMonths = [
    ['2026-05', 'May', 'paid', '2026-05-15'],
    ['2026-06', 'Jun', 'paid', '2026-06-12'],
    ['2026-07', 'Jul', 'unpaid', null],
];

foreach ($activeCustomers as $ac) {
    foreach ($billMonths as $bm) {
        $dueDay = min((int)$ac['billing_date'], 28);
        $dueDate = $bm[0] . '-' . str_pad($dueDay, 2, '0', STR_PAD_LEFT);
        $randHour = str_pad(mt_rand(8, 16), 2, '0', STR_PAD_LEFT);
        $randMin = str_pad(mt_rand(0, 59), 2, '0', STR_PAD_LEFT);
        $paidAt = $bm[3] ? $bm[3] . ' ' . $randHour . ':' . $randMin . ':00' : null;

        $billInsert->execute([
            $ac['id'],
            $bm[0],
            $ac['price'],
            $dueDate,
            $bm[2],
            $paidAt,
        ]);

        $billId = $pdo->lastInsertId();

        if ($bm[2] === 'paid') {
            $methodId = $paymentMethods[array_rand($paymentMethods)];
            $employeeId = null;
            if ($methodId == 2) {
                $empRow = $pdo->query("SELECT id FROM employees WHERE is_active = 1 LIMIT 1")->fetch(PDO::FETCH_ASSOC);
                $employeeId = $empRow ? $empRow['id'] : null;
            }

            $payHour = str_pad(mt_rand(8, 16), 2, '0', STR_PAD_LEFT);
            $payMin = str_pad(mt_rand(0, 59), 2, '0', STR_PAD_LEFT);
            $paymentInsert->execute([
                $ac['id'],
                $billId,
                $ac['price'],
                $methodId,
                $employeeId,
                $bm[3] . ' ' . $payHour . ':' . $payMin . ':00',
            ]);
        }

        echo "  Bill {$ac['id']} - {$bm[0]}: {$bm[2]}\n";
    }
}

echo "\nDummy data created successfully!\n";
echo "15 customers, " . $pdo->query("SELECT COUNT(*) FROM monthly_bills")->fetchColumn() . " bills, " . $pdo->query("SELECT COUNT(*) FROM payments")->fetchColumn() . " payments\n";