File: /home/xedaptot/ai.naniguide.com/database/seeders/Demo/Stage11_TrackingLogsSeeder.php
<?php
namespace Database\Seeders\Demo;
use App\Model\Campaign;
use App\Model\MailList;
use App\Model\SendingServer;
use App\Model\Subscriber;
use App\Model\TrackingLog;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
/**
* Stage 11 — tracking logs + open/click/bounce/feedback/unsubscribe logs
* for every campaign in STATUS_DONE.
*
* For each "done" campaign:
* - Pick min(subscriber_count_in_list, TRACKING_LOG_HARD_CAP) subscribers
* - Insert one tracking_logs row per recipient (status=sent or failed)
* - For ~OPEN_RATE of those, insert open_logs (with realistic timestamps)
* - For ~CLICK_RATE of those, insert click_logs
* - For ~BOUNCE_RATE of those, insert bounce_logs (status switches to failed)
*
* Uses bulk DB::table()->insert() in chunks of 1000.
*/
class Stage11_TrackingLogsSeeder extends AbstractDemoSeeder
{
protected int $fakerOffset = 11;
/**
* Fixed pool of 100 IPs anchored to known country-specific ISP / cloud
* ranges, distribution verified against `storage/app/GeoLite2-City.mmdb`:
* - 50 US (Comcast / Verizon / AT&T / AWS US-East / Google US)
* - 10 JP (NTT, KDDI, Softbank)
* - 10 CN (CTC, Tencent, Aliyun)
* - 10 IN (BSNL, Reliance Jio, Airtel)
* - 20 spread: GB, DE, FR, IT, CA, KR, RU, PL, BR (×2 each), AU, VN (×1)
*
* Live IPv4 addresses (no faker) — every demo run produces the exact same
* geographic distribution so screenshots / reports are reproducible. The
* GeoLite2 reader at seed time fills `ip_locations` with real country /
* city / lat-lng for each IP, so Top Countries / Open Map render properly
* (instead of faker's random IPv4 → empty geo joins).
*
* Re-verify the pool with: `php artisan tinker` →
* $r = new GeoIp2\Database\Reader('storage/app/GeoLite2-City.mmdb');
* $r->city($ip)->country->isoCode
*/
public const IP_POOL = [
// US (50)
'73.0.0.1', '73.50.100.5', '73.100.200.10', '73.150.50.20', '73.200.100.30',
'73.10.50.5', '73.250.50.5', '71.90.50.5', '71.250.50.5', '71.120.130.140',
'71.50.100.20', '71.180.100.30', '71.220.100.40', '71.150.50.10', '76.10.50.5',
'76.250.50.5', '76.50.100.150', '76.100.50.20', '76.200.100.30', '76.150.50.40',
'76.180.100.50', '98.150.50.5', '98.250.50.5', '98.120.50.30', '99.30.50.5',
'99.180.50.5', '99.50.100.10', '100.10.50.5', '104.50.100.5', '107.10.50.5',
'108.50.100.5', '68.180.100.50', '68.100.50.20', '68.200.50.40', '68.105.100.60',
'174.50.60.70', '174.100.50.80', '174.30.50.5', '47.150.50.5', '47.220.50.5',
'69.150.50.5', '69.250.50.5', '204.10.50.5', '18.208.50.10', '18.220.100.20',
'18.232.50.30', '18.205.100.40', '18.215.50.60', '52.95.50.10', '52.10.100.20',
// JP (10)
'133.242.100.10', '133.130.100.20', '133.50.100.30', '210.130.100.10', '210.150.100.20',
'211.3.100.10', '211.120.100.20', '60.70.100.10', '60.80.100.20', '126.150.100.10',
// CN (10)
'1.0.32.10', '1.180.100.20', '27.115.100.10', '27.150.100.20', '39.106.100.10',
'39.156.100.20', '60.190.100.10', '60.215.100.20', '101.4.100.10', '101.226.100.20',
// IN (10)
'49.32.100.10', '49.205.100.20', '117.255.100.10', '117.196.100.20', '157.32.100.10',
'157.50.100.20', '182.65.100.10', '182.71.100.20', '106.51.100.10', '122.180.100.10',
// Spread (20): UK, DE, FR, IT, CA, KR, RU, PL, BR (×2), AU, VN (×1)
'31.221.100.10', '82.30.100.10', // GB
'91.66.100.10', '78.46.100.10', // DE
'88.171.100.10', '92.184.100.10', // FR
'79.20.100.10', '151.5.100.10', // IT
'24.114.100.10', '70.51.100.10', // CA
'1.211.100.10', '121.139.100.10', // KR
'95.165.100.10', '77.51.100.10', // RU
'188.33.100.10', '194.181.100.10', // PL
'177.10.100.10', '189.50.100.10', // BR
'1.128.100.10', // AU
'14.160.100.10', // VN
];
public function run(): void
{
$f = $this->faker();
$allDone = [];
foreach (Stage10_CampaignsSeeder::$created as $key => $campaigns) {
foreach ($campaigns as $c) {
if ($c->status === Campaign::STATUS_DONE) $allDone[] = $c;
}
}
if (empty($allDone)) {
$this->log('no DONE campaigns — skipping');
return;
}
$defaultServer = SendingServer::whereNull('customer_id')->first();
if (!$defaultServer) {
$this->log('no system sending server found — skipping');
return;
}
$totalLogs = $totalOpens = $totalClicks = $totalBounces = 0;
$ipLocationRows = []; // collect unique IPs → country for bulk insert
foreach ($allDone as $campaign) {
// recipients = subscribers from the campaign's list, capped
$list = MailList::find($campaign->mail_list_id ?? $campaign->default_mail_list_id);
if (!$list) continue;
$cap = DemoConfig::TRACKING_LOG_HARD_CAP;
$subscriberRows = DB::table('subscribers')
->select('id', 'email')
->where('mail_list_id', $list->id)
->where('status', Subscriber::STATUS_SUBSCRIBED)
->limit($cap)
->get();
if ($subscriberRows->isEmpty()) continue;
$openRate = $f->randomFloat(2, DemoConfig::TRACKING_OPEN_RATE_MIN, DemoConfig::TRACKING_OPEN_RATE_MAX);
$clickRate = $f->randomFloat(2, DemoConfig::TRACKING_CLICK_RATE_MIN, DemoConfig::TRACKING_CLICK_RATE_MAX);
$bounceRate = $f->randomFloat(2, DemoConfig::TRACKING_BOUNCE_RATE_MIN, DemoConfig::TRACKING_BOUNCE_RATE_MAX);
$sentAt = $campaign->run_at ?? now()->subDays($f->numberBetween(1, 60));
if (is_string($sentAt)) $sentAt = \Carbon\Carbon::parse($sentAt);
// Floor for open/click timestamps so dashboard charts (last 7d /
// last 30d) always have data — even for campaigns whose run_at
// is months ago. Realistic-ish: opens may happen long after the
// send, fold them into the recent window.
$activityFloor = now()->subDays(30);
$trackingRows = [];
$openRows = [];
$clickRows = [];
$bounceRows = [];
foreach ($subscriberRows as $sub) {
$messageId = Str::uuid()->toString();
$isBounce = $f->boolean($bounceRate * 100);
$status = $isBounce ? TrackingLog::STATUS_FAILED : TrackingLog::STATUS_SENT;
$row = [
'runtime_message_id' => Str::random(20),
'message_id' => $messageId,
'customer_id' => $campaign->customer_id,
'sending_server_id' => $defaultServer->id,
'campaign_id' => $campaign->id,
'subscriber_id' => $sub->id,
'status' => $status,
'error' => $isBounce ? $f->randomElement(['SMTP error 550', 'Mailbox full', 'Domain not found']) : null,
'created_at' => $sentAt,
'updated_at' => $sentAt,
];
// Some installs add a mail_list_id column to tracking_logs, others don't.
if ($this->trackingHasMailListId()) $row['mail_list_id'] = $list->id;
$trackingRows[] = $row;
if ($isBounce) {
$bounceRows[] = [
'runtime_message_id' => $row['runtime_message_id'],
'message_id' => $messageId,
'bounce_type' => $f->randomElement(['hard', 'soft']),
'status_code' => $f->randomElement(['5.1.1', '5.2.2', '4.4.7']),
'raw' => 'demo bounce raw payload',
'customer_id' => $campaign->customer_id,
'created_at' => $sentAt,
'updated_at' => $sentAt,
];
continue;
}
if ($f->boolean($openRate * 100)) {
// Open lower bound = max(sent_at, 30-day floor). Ensures
// every open lands in the last 30 days regardless of how
// old the campaign is → "Last 7 days" / "Last 30 days"
// charts always render realistic activity.
$openLower = $sentAt->copy()->isAfter($activityFloor) ? $sentAt->copy() : $activityFloor->copy();
$openWindow = max(1, $openLower->diffInMinutes(now()));
$openedAt = $openLower->copy()->addMinutes($f->numberBetween(1, $openWindow));
if ($openedAt->isFuture()) $openedAt = now();
// Pick from the fixed 100-IP geographic pool (50 US / 10 JP /
// 10 CN / 10 IN / 20 spread). Each IP resolves through the
// GeoLite2 DB on first sight so ip_locations carries real
// country / city / lat-lng — Top Countries + Open Map render
// a realistic distribution instead of the random-IPv4 noise
// the old `$f->ipv4` produced (which mostly missed geo joins).
$ip = $f->randomElement(self::IP_POOL);
$openRows[] = [
'message_id' => $messageId,
'customer_id' => $campaign->customer_id,
'tracking_log_id' => null, // backfilled after tracking insert
'ip_address' => $ip,
'user_agent' => $f->userAgent,
'created_at' => $openedAt,
'updated_at' => $openedAt,
];
// Geocode once per unique IP. The reader is lazily booted on
// first call and reused across all campaigns in this stage.
if (!isset($ipLocationRows[$ip])) {
$ipLocationRows[$ip] = $this->resolveIpRow($ip, $openedAt);
}
if ($f->boolean($clickRate * 100)) {
$clickWindow = max(1, $openedAt->diffInMinutes(now()));
$clickedAt = $openedAt->copy()->addMinutes($f->numberBetween(1, min(60, $clickWindow)));
if ($clickedAt->isFuture()) $clickedAt = now();
$clickRows[] = [
'message_id' => $messageId,
'customer_id' => $campaign->customer_id,
'tracking_log_id' => null, // backfilled after tracking insert
'url' => 'https://acelle-demo.com/cta',
'ip_address' => $ip,
'user_agent' => $f->userAgent,
'created_at' => $clickedAt,
'updated_at' => $clickedAt,
];
}
}
}
// Bulk-insert in chunks to keep memory bounded.
foreach (array_chunk($trackingRows, 1000) as $chunk) DB::table('tracking_logs')->insert($chunk);
// Backfill tracking_log_id on open/click rows (production code
// populates this FK; without it Campaign::getTopActiveSubscribers
// and any other query that JOINs via tracking_log_id returns 0).
if (!empty($openRows) || !empty($clickRows)) {
$idMap = DB::table('tracking_logs')
->where('campaign_id', $campaign->id)
->pluck('id', 'message_id')
->toArray();
foreach ($openRows as &$r) { $r['tracking_log_id'] = $idMap[$r['message_id']] ?? null; }
unset($r);
foreach ($clickRows as &$r) { $r['tracking_log_id'] = $idMap[$r['message_id']] ?? null; }
unset($r);
}
foreach (array_chunk($openRows, 1000) as $chunk) DB::table('open_logs')->insert($chunk);
foreach (array_chunk($clickRows, 1000) as $chunk) DB::table('click_logs')->insert($chunk);
$this->insertBounceRows($bounceRows);
$totalLogs += count($trackingRows);
$totalOpens += count($openRows);
$totalClicks += count($clickRows);
$totalBounces += count($bounceRows);
$this->log(sprintf(' campaign #%d (%s): %d sent / %d opens / %d clicks / %d bounces',
$campaign->id, Str::limit($campaign->name, 30), count($trackingRows), count($openRows), count($clickRows), count($bounceRows)));
}
// Bulk-insert ip_locations so Top Countries chart has data
$ipRows = array_values($ipLocationRows);
foreach (array_chunk($ipRows, 1000) as $chunk) {
DB::table('ip_locations')->insert($chunk);
}
$this->log("totals: tracking={$totalLogs} opens={$totalOpens} clicks={$totalClicks} bounces={$totalBounces} ip_locations=" . count($ipRows));
}
protected ?bool $trackingHasMailList = null;
protected function trackingHasMailListId(): bool
{
if ($this->trackingHasMailList === null) {
$this->trackingHasMailList = in_array('mail_list_id', \Schema::getColumnListing('tracking_logs'), true);
}
return $this->trackingHasMailList;
}
/** Lazily-booted GeoLite2 reader, shared across the whole stage. */
protected ?\GeoIp2\Database\Reader $geoReader = null;
protected function geoIpReader(): ?\GeoIp2\Database\Reader
{
if ($this->geoReader !== null) return $this->geoReader;
$path = storage_path('app/GeoLite2-City.mmdb');
if (!is_file($path)) {
$this->log('GeoLite2-City.mmdb not found at ' . $path . ' — ip_locations will have NULL geo cols');
return null;
}
try {
return $this->geoReader = new \GeoIp2\Database\Reader($path);
} catch (\Throwable $e) {
$this->log('GeoLite2 reader init failed: ' . $e->getMessage());
return null;
}
}
/**
* Resolve an IP through the GeoLite2 DB and shape it for the
* `ip_locations` bulk insert. Falls back to IP-only row (NULL geo) if the
* reader is unavailable or the lookup throws — matches the post-fix
* semantics of `App\Model\IpLocation::add` (always persist the row, geo
* cols are best-effort).
*/
protected function resolveIpRow(string $ip, $createdAt): array
{
$row = [
'ip_address' => $ip,
'country_code' => null,
'country_name' => null,
'region_name' => null,
'city' => null,
'zipcode' => null,
'latitude' => null,
'longitude' => null,
'created_at' => $createdAt,
'updated_at' => $createdAt,
];
$reader = $this->geoIpReader();
if (!$reader) return $row;
try {
$r = $reader->city($ip);
$row['country_code'] = $r->country->isoCode;
$row['country_name'] = $r->country->name;
$row['region_name'] = !empty($r->subdivisions) ? $r->subdivisions[0]->name : ($r->city->name ?? null);
$row['city'] = $r->city->name;
$row['zipcode'] = $r->postal->code;
$row['latitude'] = $r->location->latitude;
$row['longitude'] = $r->location->longitude;
} catch (\Throwable $e) {
// Unknown range / DB miss — leave geo cols NULL but keep the IP.
$this->log('GeoLite2 miss for ' . $ip . ': ' . $e->getMessage());
}
return $row;
}
/**
* bounce_logs schema may differ across installs — feature-detect columns.
*/
protected function insertBounceRows(array $rows): void
{
if (empty($rows)) return;
$cols = \Schema::getColumnListing('bounce_logs');
$filtered = [];
foreach ($rows as $row) {
$clean = [];
foreach ($row as $k => $v) {
if (in_array($k, $cols, true)) $clean[$k] = $v;
}
$filtered[] = $clean;
}
try {
foreach (array_chunk($filtered, 1000) as $chunk) DB::table('bounce_logs')->insert($chunk);
} catch (\Throwable $e) {
$this->log('bounce_logs insert failed: ' . $e->getMessage());
}
}
}