# 06 — Admin panel, reports, marketing, notifications & scheduled jobs > Written 2026-09-29 against branch `docs/codebase-map-2026-09-29` (= `origin/dev`), by reading the code only. > Nothing was run: no app, no DB, no tests. Every `file:line` was read in the code. > Claims that were inferred but not traced end to end are marked **UNVERIFIED**. > Line numbers drift, so grep for the quoted symbol if a line no longer matches. Abbreviations used in this file: | Short | Full path | |---|---| | **RC** | `app/Http/Controllers/ReportController.php` (3,841 lines) | | **RFS** | `app/Services/ReportFilterService.php` | | **PMS** | `app/Services/PlatformMetricsService.php` | | **WCU** | `app/Utility/WhatsAppCampaignUtility.php` | | **FNT** | `app/Traits/FirebaseNotificationTrait.php` | | **NU** | `app/Utility/NotificationUtility.php` | --- ## 1. Purpose & scope This file covers the back office of kapitano.shop: the admin shell (`routes/admin.php`, the sidebar `resources/views/backend/inc/admin_sidenav.blade.php`, the `IsAdmin` middleware, Spatie permissions); dashboards, reports and CSV/XLSX exports; business settings; marketing (WhatsApp campaigns and templates, cart retargeting, loyalty, membership, referrals, customer segmentation); every notification channel (DB, FCM push, seller PWA push, WhatsApp, email, SMS, Facebook CAPI, GA4); and console commands and the scheduler. Read these parts of `CLAUDE.md` first: the price contract (RAW / BASE / SYP, `order_rate_sql()`, `format_syp()`), "Admin reports share one filter layer", and "Marketing audiences share one segmentation layer". ### How admin access works There are three layers: 1. **`auth`, then `admin`.** `admin` is `App\Http\Middleware\IsAdmin`. It lets through `user_type` `admin` or `staff`, returns 404 to everyone else, and forces staff password rotation (`StaffPasswordPolicy::isExpired()`). 2. **`permission:`.** This is Spatie `PermissionMiddleware` (`app/Http/Kernel.php:92`). It is usually declared in the controller constructor with `->only(...)`, and sometimes on a route group. 3. **`Gate::before`.** In `app/Providers/AuthServiceProvider.php:29`, the `Super Admin` role passes every check. Sidebar `@can` only hides links. A controller method with no `permission:` entry is open to **every staff account**. The main admin group opens at `routes/admin.php:~163`: ```php Route::group(['prefix' => 'admin', 'middleware' => ['auth', 'admin', 'prevent-back-history']], ...) ``` Everything before it and after its closing `});` (`admin.php:1026`) is outside that protection (see §8). --- ## 2. Admin menu map Permission = what the controller or route **enforces**. A sidebar-only `@can` is noted as such. ### Dashboards | Menu item | Route name → URL | Controller@method | Permission | |---|---|---|---| | Dashboard | `admin.dashboard` → `/admin` | `AdminController@admin_dashboard` (:44) | **none on the route**. The view hides blocks with `@can('admin_dashboard')` (`backend/dashboard.blade.php:17`). | | Dashboard AJAX blocks | `dashboard.*` POSTs (`admin.php:255-259`) | `AdminController@top_category_products_section` etc. (:271-419) | none, beyond admin/staff | | Investor Report | `reports.investor` → `/admin/reports/investor` | `Admin\Report\InvestorReportController@index` | `investor_report` (ctor :24) | | Kapitano Growth | `reports.kapitano_growth` → `/admin/reports/kapitano-growth` | `Admin\Report\GrowthReportController@index` | `kapitano_growth_report` (ctor :22) | | (old bookmark) | `investor.dashboard` → `/admin/dashboard/inv` | redirects to kapitano-growth (`admin.php:672`) | — | | Dashboard Reports | `admin.dashboard.report.view` → `/admin/report/view`; data from `admin.dashboard.report` | `AdminController@admin_dashboard_report(_view)` (:189, :196) | `dashboard_reports` (ctor :32) | | Products Reports | `categories.products` → `/admin/categories/products/1` | `CategoryController@products` | `products_report` (`CategoryController.php:23`) | ### Sales and Customers → Loyalty Program | Menu item | Route name | Controller@method | Permission | |---|---|---|---| | Sales → Cart Logs | `cart-logs.index` | `CartLogController@index` | `view_all_orders` (ctor :15) | | Loyalty Points | `loyalty.index` | `LoyaltyController@index/edit/update` | `view_all_loyalty` / `edit_loyalty` | | Membership | `membership.index` | `MembershipController@*` | `view_all_membership` / `edit_membership` | | History | `loyalty_logs.index` | `LoyaltyLogController@index` | `view_loyalty_logs` | | Order Setting Info (points per SYP) | `setting.edit` / `setting.update` | `SettingController` | `edit_setting` (whole controller) | | Guidance Guide | `fixed_page.index` | `FixedPageController` | `view_all_fixed_pages` / `edit_fixed_page` | | Register Invitations | `register-invitations.index` | `RegisterInvitationController@index` | `view_register_invitations` | ### Reports The group is wrapped in `@canany([...])` at `admin_sidenav.blade.php:~1303`. URLs are under `/admin/`. | Menu item | Route name → URL | Controller@method | Permission | |---|---|---|---| | Earning Report | `earning_payout_report.index` → `reports/earning-payout-report`, plus POST `net-sales`, `payouts`, `sale-analytic`, `payout-analytic` | `EarningReportController@index` (:48) | `earning_report` | | Profit Report | `earning_payout_report.profit` | `EarningReportController@profitReport` (:31) | `earning_report` | | In House Product Sale | `in_house_sale_report.index` | RC `in_house_sale_report` | `in_house_product_sale_report` | | Seller Products Sale | `seller_sale_report.index` | RC `seller_sale_report` | `seller_products_sale_report` | | Products Stock | `stock_report.index` | RC `stock_report` | `products_stock_report` | | Stock zeroing | `stock_zeroing_report.index` | RC `stock_zeroing_report` | `stock_zeroing_report` | | Products wishlist | `wish_report.index` | RC `wish_report` | `product_wishlist_report` | | Cart Report | `cartreport.index` → `cart-report`; `cartreport.export` | `CartLogController@index2/exportData` | `cart_report` | | Most Sale Items Report | `item.top` → `top_sale_item` | RC `item_top_sales` | `most_sale_items_report` | | Sales & Profit Report | `reports.sales_profit_report` (+ `/export`) | RC `salesProfitReport` / `exportSalesProfitReport` | `sales_profit_report` | | AOV | `reports.aov` | RC `aovReport` | `aov_report` | | Cart Activity | `reports.cart_activity` | RC `cartActivity` | `cart_activity_report` | | Repeat Purchase | `reports.repeat_purchase` | RC `repeatPurchaseRate` | `repeat_purchase_report` | | Top Customer Report | `customer.top`, `customer.top.details`, `reports.customer.export` | RC `top_customers`, `top_customer_details`, `exportTopCustomer` | `top_customers_report` | | Top Categories Report | `categories.top` | RC `top_categories` | `top_categories_report` | | Sales Report By City | `city.top` | RC `sales_by_city` | `sales_by_city_report` | | Coupon Usage Report | `coupon_usage_report.index` / `.export` / `.customer` / `.show` | `Admin\Report\CouponReportController` | `coupon_usage_report` (ctor :27) | | Seller Response Time (+ the merged Seller Orders report) | `seller_response_report.index` (`?panel=` seller panel by AJAX, `&export=orders_csv`; `?open=` opens it) / `.export` | `SellerResponseReportController` + `App\Services\SellerOrdersReport` | `seller_response_report` **or** `seller_orders_report` (route group, `admin.php:788`) | | ~~Seller Orders Report~~ (طلبات التجار) | `seller_orders_report.index` / `.show/{seller}`: **redirects** to Seller Response Time (`?open=`) since 2026-10-19; no menu entry | `SellerOrdersReportController` (redirects only) | either permission (`seller_orders_report` came with migration `2026_10_14_000001`, granted to every holder of `seller_response_report`) | | Seller Inventory Updates (تحديث مخزون التجار) | `reports.seller_inventory` → `reports/seller-inventory` (`&export=csv`, `&fresh=1` recounts) / `reports.seller_inventory.show` → `reports/seller-inventory/{userId}` | `Admin\Report\SellerInventoryReportController` + `App\Services\SellerInventoryActivity` | `seller_inventory_report` (ctor; migration `2026_10_24_000001`, granted to every holder of `seller_response_report`) | | Order Status Times (تقرير معدل تغيير حالة الطلبات) | `reports.order_status_times` → `reports/order-status-times` (`?range=` placement period, default last 30 days; `?employee=|none`; `&export=csv` one line per purchase) | `Admin\Report\OrderStatusTimeReportController` + `App\Services\OrderStatusTimes` (its docblock is the map): every status against every other incl. paid / cancelled / returned (`matrix()`, a pair going back in time left out) for the **purchases** placed in the period (combined order; a status is reached when its last live seller order reaches it), and the ones still open by the status they wait in (`stillOpen()`: since placed / since reaching it); all purchases or one follow-up employee's (`order_staff_details.follow_up_user_id`). Computed in PHP from the orders' status times (0.06 s for 1,600 purchases locally), no cache | `order_status_time_report` (ctor; migration `2026_10_28_000002`, granted to "full access") | | Least / Most Sale Product | `reports.least_sold_products` / `reports.most_sold_products` | RC `leastSoldProducts` / `mostSoldProducts` | `least_sold_products_report` / `most_sold_products_report` | | Product category distribution | `reports.product_category_distribution` | RC `productCategoryDistribution` | `product_category_distribution_report` | | Order Time Report | `reports.order_time_report` | RC `orderTimeReport` | `order_time_report` | | Customer Profiles (was Customer Demographics) | `reports.demographics` | RC `customer_demographics` | `customer_demographics_report` | | Cart Retargeting | `cartretargeting.index` / `.update` / `.claim` / `.release` | `CartRetargetController` | `cart_retargeting` (ctor :29) | | Order ratings → Follow-up (متابعة العملاء الذين لم يقيّموا) | `admin.order_ratings.follow_ups.index` / `.export` / `.take` / `.record`; `.assign` / `.unassign` / `.reopen` | `OrderRatingFollowUpController` + `App\Services\OrderRating\FollowUps` (§4.3a) | `follow_up_order_ratings`; the last three also `assign_order_rating_follow_ups` (migration `2026_10_27_000001`) | | Order ratings → Follow-up after rating (المتابعة بعد التقييم) | `admin.order_ratings.after_rating.index` / `.export` / `.record`; `.reopen`; customer notes `admin.order_ratings.customer_notes.store` / `.update` / `.destroy` | `LowRatingFollowUpController`, `OrderRatingFollowUpController` + `LowRatingCases` / `LowRatingList` / `CustomerNotes` (§4.3a) | open: `view_order_ratings` or `follow_up_order_ratings`; acting `follow_up_order_ratings`; reopen also `assign_order_rating_follow_ups` | | User Searches | `user_search_report.index` (`?platform=web|app|android|ios`, `&searcher=customer|guest`, `&min_count=`, `&export=csv`) | RC `user_search_report`: counts the searches made **in the period** from `search_events` (`search_submitted`: website `/search` from any page's box + the app's `v2/search/track`; dated since 2026-08-25), per term with website / app split and signed-in customers. Never read the old `searches` table for a period: it holds one all-time counter per term and only its last date (until 2026-11-10 "today" listed today's terms with their all-time totals). Clicks are not shown: most app clicks carry no term and the website results page records none. Pinned by `UserSearchReportTest` | `user_search_report` | | Commission History | `commission-log.index` | RC `commission_history` | `commission_history_report` | | Wallet Recharge History | `wallet-history.index` | RC `wallet_transaction_history` | `wallet_transaction_report` | | Products added (Reports menu) | `admin.reports.products_added` → `reports/produtcs_added` (sic) | RC `productReportAdded` | `products_added_report` | | Change Log | `audit_log.index` | `Admin\AuditLogController@index` | only `user_type == admin` (sidebar check and controller) | ### Marketing | Menu item | Route name | Controller | Permission | |---|---|---|---| | Flash deals / Dynamic Pop-up / Custom Alert | `flash_deals.index`, `dynamic-popups.index`, `custom-alerts.index` | CMS controllers | `view_all_flash_deals`, `view_all_dynamic_popups`, `view_all_custom_alerts` | | Email Templates (admin/seller/customer/all) | `email-templates.index` → `/admin/email-template/{type}` | `EmailTemplateController` | `manage_email_templates` | | Newsletters | `newsletters.index`, `newsletters.send`, `test.smtp` | `NewsletterController` | `send_newsletter` | | Notification → Settings | `notification.settings` | `NotificationController@notificationSettings` | `notification_settings` | | Notification Types | `notification-type.*` | `NotificationTypeController` | `view_all_` / `add_` / `edit_` / `delete_notification_types` | | Custom Notification + Scheduled | `custom_notification`, `custom_notification.scheduled*` (about 13 routes, `admin.php:981-1005`) | `NotificationController` | `send_custom_notification` | | Custom Notification History | `custom_notification.history` | `NotificationController` | `view_custom_notification_history`; deletes need `delete_custom_notification_history` | | WhatsApp Campaigns | `whatsapp_campaigns.*` (`admin.php:706-727`) | `Admin\WhatsAppCampaignController` | `manage_whatsapp_campaigns` (route group) | | WhatsApp Templates | `whatsapp_templates.*` (`admin.php:732-743`) | `Admin\WhatsAppTemplateController` | `manage_whatsapp_campaigns` (route group) | | Bulk SMS | `sms.index` (`routes/otp.php:47`) | OTP addon | `addon_is_activated('otp_system')` and `send_bulk_sms` (sidebar) | | Subscribers / Coupon / App Popup | `subscribers.index`, `coupon.index`, `app-popups.index` | CMS / Kapitano | `view_all_subscribers`, `view_all_coupons`, `view_all_app_popups` | | Splash screen (2026-11-11) | `splash-screens.*` | `Admin\SplashScreenController` (rules: `App\Services\SplashScreens`, app contract `docs/api/splash-screen.md`) | `view_splash_screens` looks, `manage_splash_screens` changes | ### Setup & Configurations Controller: `BusinessSettingsController`, constructor at :23-64. - Page permissions: - `features_activation` - `smtp_settings` - `otp_configurations` (OTP provider page and its test) - `payment_methods_configurations` - `order_configuration` - `file_system_&_cache_configuration` - `social_media_logins` - `whatsapp_chat` - `facebook_comment` - `analytics_tools_configuration` (also the pixel writer) - `google_recaptcha_configuration` - `google_map_setting` - `google_firebase_setting` - `shipping_configuration` - `general_settings` - `seller_commission_configuration` - `seller_verification_form_configuration` - The shared writers need **`update_settings`** (:49): `update`, `updateActivationSettings`, `updatePaymentActivationSettings`, `env_key_update`, `import_data`. - The sidebar shows "Staff Password Policy" (`staff_password_policy.index`) under `general_settings`. **UNVERIFIED:** which permission the method itself enforces. It is not in the constructor list. - "Seller WhatsApp Order Alerts" is `SellerWhatsAppSettingsController` with `permission:seller_whatsapp_settings` (route group, `admin.php:676`). - "App Update Settings" is `app.settings` → `PageController@indexApplicaion`. It is registered **outside** the auth/admin group (`admin.php:~128`). It is protected only by `permission:edit_website_page` in `PageController.php:23`: guests are refused, but `IsAdmin` and password rotation are skipped. ### Permission-reference drift (`docs/permissions-reference.md`) - `stock_zeroing_report` and `manage_whatsapp_campaigns` are used in code and seeded by migrations `2026_09_20_000002` and `2026_09_24_000002`, but are **missing** from the doc. - `view_kapitano_growth` is listed in the doc but used nowhere. The code uses `kapitano_growth_report`. --- ## 3. Reports reference ### 3.1 Shared layer: `ReportFilterService` (RFS) `filters(Request, $extra)` (:39-93) returns: `range` (20 presets, `dateBounds` :98), `from`, `to`, `delivery_status[]` (whitelisted), `payment_status`, `payment_type`, `seller_id`, `entry_type` (seller entry type: `self` / `managed` / `unclassified`, '' = all — `App\Services\SellerEntryType`), `category_id`, `brand_id`, `city`, `search`, `exclude_cancelled`, `per_page` (default 25) and `sort`. `entry_type` is applied by `applyOrderFilters()` on `$table.seller_id`; a report that filters sellers on another column (`products.user_id` in the stock reports and the growth report's products, `shops.user_id` in the seller sale report, `n.seller_id` in the seller response report) calls `applyEntryType($q, $f, $column)` next to its `seller_id`. `_filters` shows the select whenever `seller` (or `entry_type`) is in `show`. `exclude_cancelled` **defaults to true**. It is read from the checkbox only when `_submitted` is present (:87). It drops both `cancelled` and `returned` orders (label "Exclude cancelled and returned orders"). **`count_by` ("Count Orders By", `RFS::COUNT_BY`, since 2026-10-14)** — `checkout` (**default**: a purchase split between sellers counts once, `combined_order_id`, else the order itself; `checkoutKeySql()`) or `seller` (one per `orders` row). Every order count, average per order, "repeat" and rate of an order count goes through `orderCountSql($f)` (SQL, `COUNT(DISTINCT …)`, safe with lines joined) or `orderCount($rows, $f)` (loaded rows); quantities, lines, customers, visits and carts never do. `queryString()` carries it; `_filters` shows it with `'count_by'` in `show`. Wired in: top customers (+ details "in the filters" numbers), sales by city, most sold, top categories, in-house sales, seller sales (in basket mode the totals count distinct purchases, not the sum of sellers), investor (`PMS::orderSummary()`: `orders`/`aov`/`repeat`/`frequency`/rates follow it, `checkouts` is always per purchase, `seller_orders` always per row), growth, dashboard analytics, traffic sources, seller profitability (`SellerPricingReport::orderTotal()` for the total), and the pre-RFS pages through `shared/_count_by_form` or their own form: AOV (highest/lowest = per purchase total; the monthly chart now sums SYP), repeat purchase, customer demographics, order time, sales profit, coupon usage (`finishStats($row, $f)`: averages per use or per seller order). Pinned by `ReportCountByTest` + both modes in `DashboardAnalyticsTest`, `PlatformMetricsServiceTest`, `CouponUsageReportTest`, `TopCustomersLifetimeValueTest`. `applyOrderFilters($q, $f, $table='orders', $epochDate=false)` (:194-222): - The date column is `orders.created_at` **unless `$epochDate=true`**. Only PMS passes `true` (`PMS:370`). - `created_at` is stored 8–9 h off the app timezone (comment at :190), so every RC report built on RFS ranges on the skewed column. - A `delivery_status` filter overrides `exclude_cancelled`. Money and output helpers: | Helper | Line | What it returns | |---|---|---| | `lineRevenueSql()` | :234 | `price + tax`, BASE | | `lineRevenueSypSql()` | :244 | `lineRevenueSql() × order_rate_sql` | | `orderAmountSypSql($col)` | :250 | `$col × order_rate_sql` | | `streamCsv()` | :305 | UTF-8 BOM, streamed when given a generator. RC `rowsForExport()` (:3772) uses `cursor()`. | Views are in `resources/views/backend/reports/shared/`: `_filters`, `_header`, `_kpis`, `_growth`, `_datatables`, `_scripts`, `_styles`, `_customer_styles`, `_customer_details_modal`, `_select_field`, `_number_field`, `_checkbox_field`, `_count_by_form`. Pages on the shared layer: cart_retargeting, seller_inventory, investor, kapitano_growth, in_house_sale, seller_sale, most_sale_item, sales_by_city, top_categories, top_customers, seller_response, stock_report, stock_zeroing, user_search, wish_report. Everything else uses its own hand-rolled old UI and filters. A fact several findings below depend on: **`order_details.price` is the line total (unit × qty).** Evidence: `OrderController.php:648` sets `cart_product_price(...) * quantity`, and `OrderController.php:386` divides by quantity to get the unit price. ### 3.2 Report table Legend: "order rate" = `order_rate_sql` (rate on the order date) = correct. "today" = `single_price` / `convert_price` = wrong for past orders. | Report | Location | Computes | Currency / rate | Cancelled | |---|---|---|---|---| | Customer demographics | RC `customer_demographics` -> `App\Services\Customers\CustomerDemographics` (rebuilt 2026-11-09, see 4.2a) | Account only: active customers by registration or profile-completion date; profile completeness, gender, age (under 18 to 55+, not recorded), governorate, what is missing, the customers list + CSV | none (no money) | n/a (no orders) | | Products added | RC `productReportAdded` | Products created in range, list-price and cost totals; filter "added by" (merchants / employees, `ProductCreator::constrain()`), column + counts of who added each product | rows `product_price_to_syp()` (RAW at the product's rate → SYP), totals `raw_to_base_sql` → BASE → `convert_price()`; min/max price typed in SYP (`syp_price_bounds_to_base`) | n/a | | Order time | RC:638-855 | Counts by hour, weekday and month (`created_at`) | no money | **Included** | | Most sold products | RC:859-1048 | Units and revenue per product | order rate, but see bugs | **Included** | | Least sold products | RC:1054-1261 | Same, plus never sold | order rate, same bugs | **Included** | | Category distribution | RC:1272-1421 | Product count per direct `category_id` (no subtree, unpublished included) | none | n/a | | Sales & Profit | RC:1440-2201; export RC:2203 → `App\Exports\SalesProfitReportExport` | Sales, "cost", profit, per-day/vendor charts, cancel rate and reasons | **BASE, never converted**. `SUM(od.price)` (:1640-1647); view prints raw `{{ $totalSales }}` (`sales_profit_report.blade.php:459-493`) | **Included** unless a status is picked; cancellations reported separately (:1593, :2139) | | AOV | RC:2211-2305 | Month/year AOV, highest order, monthly chart | KPIs SYP at order rate (:2229); **chart is BASE `SUM(grand_total)`** (:2262) | Excluded (:2220, :2242, :2250, :2266); paid only | | Repeat purchase | RC:2306-2407 | Customers with more than one paid order **in the same calendar month** | none | **Included** (paid only) | | Cart activity | RC:2411-2513 | `carts` rows per month; list and top customers | `single_price(carts.price)` (today; fine for live carts) | n/a | | Stock zeroing | RC:2546 (query :2591) | Stock zeroed by "unavailable" cancellations, item deletes and seller reports (`stock_zeroings.created_at`; "Zeroed by" tags seller reports) | none | n/a | | Products stock | RC:2661 (query :2713) | Qty and value per product | `raw_to_base_sql(ps.price) * qty` (:2718); view `single_price`, CSV `convert_price` (:2701), both today's rate, which is right for stock | n/a | | Top categories | RC:2792 (query :2838) | Revenue per category; `level=root` rolls up | `lineRevenueSypSql` ✔ | RFS default (excluded) | | Most sale items | RC:2933-3047 | Order KPIs, status donut, top 15 lines, trend | `orderAmountSypSql` / `lineRevenueSypSql` ✔ | RFS; the donut forces `exclude_cancelled=false` (:2953) | | Top customers | RC:3053 (query :3096) | Spend per customer (`user_type=customer`); column + sort `ltv` (`customerLifetimeValueQuery()`) = **delivered** orders only, minus their cancelled/returned lines (price + tax — `grand_total` never shrinks for them; `refund_requests` is not installed), floored at 0 per order, **over the report's filters** (the details page shows the whole lifetime, same query without filters); under it "N× the average". KPIs "Average LTV" (Σ LTV / listed customers with a delivered order, with the store-wide `storeLifetimeValue()` and the % gap under it) and "Median LTV" (`medianLifetimeValue()`). Filter `count_by` = `checkout` (default: a purchase split between sellers counts once, `combined_order_id`) or `seller` (every `orders` row) drives orders, average order, "Repeat" and min orders. Pinned by `TopCustomersLifetimeValueTest` | `orderAmountSypSql` ✔ | RFS default | | Top customer details | RC:3153-3315 | One customer: orders, AOV per checkout, products | SYP ✔ (:3180-3181) | Lifetime stats exclude cancelled (:3193, :3261) | | Sales by city | RC:3321 (query :3360) | Revenue per JSON `shipping_address.city` | ✔ | RFS default | | In-house product sale | RC:3406 (query :3446) | Qty and revenue per admin product, from order lines | ✔ `lineRevenueSypSql` | RFS default | | Seller products sale | RC:3504-3599 | Revenue per shop | ✔ `orderAmountSypSql` | RFS default | | Wishlist | RC:3605-3693 | Wishes per product | none; the CSV "Total Sales" is the lifetime `num_of_sale` (:3683) | n/a | | User searches | RC:3699-3767 | `searches` by `updated_at` | none | n/a | | Commission history | RC:3779-3809 | `commission_histories`, paginated 10 | **raw BASE numbers, no currency** (`partials/commission_history_section.blade.php:55-56`) | n/a | | Wallet history | RC:3811-3839 | `wallets` rows | `single_price` (today) (`wallet_history_report.blade.php:67`) | n/a | | Top customer XLSX | RC:56 → `TopCustomersExport` | Its own query: all user types, BASE | BASE unconverted | Excluded | | Dashboard | `AdminController.php:44-185` | Totals, charts, top lists | Mixed; see below | Mixed: `total_sale` is delivered only (:112); others exclude only cancelled | | Dashboard Reports page | `AdminController.php:196-261` | Monthly **counts** (`total_sale` is a count of delivered orders) | none | — | | Earning report | `EarningReportController.php:48-215` | Monthly sales chart, payouts, top categories/brands | `total_syp` order rate (:52, :57); subscriptions at today's `currency_syp_factor` (:126); payouts `single_price` | Delivered only | | Profit report | `EarningReportController.php:31` + `profit_report.blade.php:47` | `num_of_sale × (unit_price − purchase_price)` | **RAW prices, lifetime counter** | n/a | | Coupon usage | `CouponReportController` + `app/Services/CouponUsageReportService.php` (694 lines) | Per coupon / customer / order; legacy orders show as "Unknown" | SYP order rate (service :315, :364); Orders sheet uses `convert_order_price` | **Included**: its own `filters()` near-copy of RFS without `exclude_cancelled` (service :64, :143, :191; `baseQuery` :247-306) | | Kapitano Growth | `GrowthReportController.php` + PMS | New customers/sellers/products (`created_at`), orders/GMV/buyers (`orders.date`), health, seller and category growth | SYP per order via `order_exchange_rate` (`PMS:352-364`) | RFS default via `PMS::counts` (:400) | | Investor | `InvestorReportController.php` + PMS | Period summary, sellers, categories, cities, cohorts | SYP | RFS default; `cohorts()` loads **every** non-cancelled order on each view (`PMS:565-570`) | | Seller response | `SellerResponseReportController.php` | Alert → answer medians and P90, grades A–D/X (min 10 alerts, :284-312) | none | Ignores status filters; excludes `closed_reason=cancelled` (:194-200) | | Seller orders (columns + seller panel of Seller Response Time, merged 2026-10-19) | `SellerOrdersReport` (service, map in its docblock) | **Only the seller own answer counts, whatever staff did** (decided 2026-10-14). Per seller order `state` = unavailable (`orders.seller_unavailable_at` / alert "out of stock") > seller_confirmed (`seller_confirmed_at` / "accepted", not when staff confirmed on their behalf: alert `staff_on_behalf` or follow-up log `seller_confirmed_by_staff`) > cancelled (for a seller reason: `OrderCancelReasons::SELLER_UNAVAILABLE` / `SELLER_FAULT`) > cancelled_other (any other reason, none, or returned - **not held against the seller**, decided 2026-10-19) > awaiting (also when staff confirmed / delivered it; `staff_handled` flags those). The table shows: received = **confirmed** (in full + partly: some, not all, products flagged) + **not confirmed** (the seller flagged every product or named none - only what the seller answered) + **cancelled for another reason** + **awaiting** (shown as "Seller did not answer" / لم يردّ التاجر: the silent orders **plus `cancelled`**, the ones our staff cancelled for a seller reason - decided 2026-10-19; `awaiting_staff` = how many of them our staff handled), and **unavailable products** (flagged products of partial orders + every product of the orders reported wholly unavailable or cancelled as `out_of_stock`, **+ the products our staff deleted from an order with "delete and set its stock to zero"** - decided 2026-10-20, read from `stock_zeroings` source `order_item_delete` as `staff_removed_lines`; they count in the order products (`totalLinesOf()`, `judged_lines`), are listed with the order marked "deleted by our staff as not available" with who did it, show apart as `staff_removed` / `staff_times`, never change the seller answer, and an order cancelled for another reason holds none; the panel shows the reported variant's stock now, from `product_stocks`, never `products.current_stock`); every rate out of the orders judged (`judged` = received - cancelled other; products out of `judged_lines`). The seller panel adds the score card (same numbers as the row), `cancelBreakdown()` (reasons held against the seller, then the others by `OrderCancelReasons` group, returned, none; each a `cancel_reason` filter) and the orders with their products, sorts by each rate both ways (`RATE_SORTS`), a column heading sorts by its rate too | `orderAmountSypSql` ✔ | RFS on `orders.date` (epoch), `exclude_cancelled` forced off, default last 30 days; only orders since `Order::SELLER_CONFIRMATION_SINCE`, in-house left out. Alert columns filter on `seller_order_notifications.created_at`, order columns on `orders.date`; an alert counts once it went out (`send_status = accepted`), **also when the order was cancelled before the seller answered** (an unanswered alert, decided 2026-11-08, so a seller's alerts match their orders received; failed sends, no number and orders with no alert stay out); sellers with orders but no alert are listed under the graded ones; the panel is paginated | | Seller answer on Admin → Orders (2026-10-26) | `SellerOrdersReport::answersFor()` / `listBadge()` / `waitingForSeller()` / `lineAnswer()` / `orderIdsWithAnswer()`, read by `OrderController::all_orders` | The orders list uses the report's own rules, so its labels match the seller dashboard and Seller Response Time. **Checkout row**: no status of its own (decided 2026-10-26); "بانتظار تأكيد البائع x/n" only while a seller is still awaited (`waitingForSeller()`: no answer, the order neither cancelled / returned nor moved on by our staff - confirmed for them, shipped, delivered; older orders: still pending, nothing answered), x = the open seller orders not awaited, n = the open seller orders (not in-house, not cancelled / returned); otherwise nothing. **Each seller-order row** (and its "+" panel header) shows `listBadge()`: مؤكد كلي / مؤكد جزئي / غير مؤكد; بانتظار تأكيد البائع while still awaited; لم يردّ التاجر once the order moved on without an answer - our staff confirmed / shipped it, or cancelled it for a reason held against the seller - the report's own (`OrderCancelReasons::SELLER_UNAVAILABLE` + `SELLER_FAULT`: product not available, seller late, wrong price or description; not "rest of the order not available", usually another seller's products, confirmed 2026-10-26) - then "ملغى من قبل الإدارة" under it. A seller who answered keeps their answer whatever happened next, with who closed the order under it - exactly the reports' state, so the list, Seller Response Time, Seller Inventory Updates and the seller dashboard count every order alike (decided 2026-10-26). An order the seller never answered and that was cancelled for another reason or returned (the reports' cancelled_other) shows only who closed it: ملغى من قبل العميل (the customer cancelled it - `orders.cancelled_by` = the order's customer, as the order timeline reads it - or our staff did for a reason of the cancel list's "Requested by the customer" group) or ملغى من قبل الإدارة (anyone else, or nobody recorded), مرتجع for a return; the reason is the tooltip. The "Seller Answer" filter keeps the dashboard's five answers (its لم يردّ التاجر holds both the awaited and the moved-on ones). **Each product** in the "+" panel: متوفر / غير متوفر (flagged, or the whole order reported missing) / بانتظار التاجر, and the products our staff deleted as not available are listed struck through (`staffRemovedLinesFor()`). The "Seller Answer" filter (`seller_confirmation` = `OrderController::SELLER_ANSWER_FILTERS`; the old `confirmed` reads as `confirmed_full`) is `whereIn(orders.id, orderIdsWithAnswer())`. Orders the report does not judge (in-house, before `Order::SELLER_CONFIRMATION_SINCE`) keep `Order::sellerConfirmationBadge()`; the order page, combined order page and follow-up screens still use that badge | none | one query per page (`order_ids` narrows every sub-query of `ordersQuery()`) | | Seller inventory updates (2026-10-24) | `SellerInventoryActivity` (report) + `SellerStockUpdates` (the record; maps in their docblocks) | Does each seller update their own stock, and how often, and how many orders they refused as out of stock. **Source: its own table `seller_stock_updates`, never the audit log** (it can be pruned): written by the `ProductStock` / `Product` hooks when a seller who owns the product changes a quantity (`kind = qty`, before / after) or edits the product (`kind = save`, `stock_rows`). The product form (web and app) recreates every variant even for a quantity-only change, so a save is judged at the end of the request (`SellerStockUpdates::flush()`): the variants kept at `Product::saving` against the new ones - a quantity that differs is a `qty` row, and a `save` row only for a variant added / removed, a variant price / SKU / photo changed, a `CONTENT_FIELDS` change, a new product or a save that changed nothing; `actor = staff` + `staff_id` under "Log in as seller"; order stock moves (`OrderStockUtility` inside `quietly()`, `/order` and `/so/` paths), admin screens, partner sync and the console are never recorded. History before the table copied from the audit log by migration `2026_10_24_000002` and again by `2026_10_25_000001` with the same comparison, rebuilt from the audit trail (`source = backfill`; prices are not compared there, the audit holds them before the price rules); `business_settings.seller_stock_updates_since` = start of the record (2026-09-20), every period is cut to it. Status: partner store > no products on sale > regular (an update in >= 50% of the weeks, Saturday weeks) > occasional > staff only > idle > never. Per seller: weeks with an update + a week strip, update days, quantity edits, product saves, products changed, days between updates, last update (seller / staff), variants out of stock now, products not updated in N days, live orders received and **orders refused as out of stock** - every order figure is Seller Response Time's own, read from `SellerOrdersReport` (`sellersQuery()` / `ordersOf()` / `rates()`, unified 2026-10-26, never counted again here): orders received (cancelled included), refused (= its Partly + Not confirmed), refused whole (= Not confirmed), refused products (= Unavailable products), the refusal rate (= its `unavailable_rate`, out of the orders it judges) and the refused orders list, over the date range the user picked (`orderRange()`), not the period cut to the start of the stock record; the order cards are its totals (every seller, partner stores included even when hidden), the stock cards leave partner stores out. Written summary (`findings()`) and "contact first" (`contacts()`, refusals first). The sellers table is paged (`per_page(25)`); cards, bar, summary and CSV cover every seller; cached 10 minutes per period and stale window | none (counts) | Orders received: `orders.date` in the period, `void_order_statuses()` left out; refusals count every status | | Cart report | `CartLogController@index2` (:43) / `exportData` (:79, `CartReportExport`) | Raw listing of `carts` rows (user/guest phone, product, date) | none | n/a | ### 3.3 Verified report bugs **Most / least sold products double-count** - `price * quantity` at RC:913, :978 and :1109. `order_details.price` is already the line total, so revenue is inflated by the quantity factor, and tax is omitted. - The `leftJoin product_stocks` on `product_id` (RC:~891, :~1087) multiplies both `sold_qty` and revenue by the number of variants. - For least sold, the date filter sits in WHERE on a left-joined table (:1119-1138), so never-sold products disappear once dates are set. - The same `price * quantity` bug is in the dashboard seller-products block (`AdminController.php:380`). **Sales & Profit "cost" is not a cost** - Cost = `product_stocks.price`, i.e. the current **selling** price (:1655-1700; the in-code comment admits it). So "profit" is roughly today's price minus the historic price. - The vendor chart uses `COUNT` instead of `SUM` (:2101, :2104). - All amounts are BASE printed raw. **Earning report missing braces** At `EarningReportController.php:118-132`: ```php if ($product_sale->time == $month) $sale_stat_data['total'] += ...; $sale_stat_data['total_syp'] += ...; // runs for every row ``` Only the first statement is conditional. `total_syp` therefore adds every month's sales into every month, so the SYP chart and `sales_this_month` are wrong. The same pattern repeats for seller and customer subscriptions. **Earning index writes to the DB on GET:** `packageAmountStoreIntoPackagePaymentTable()` runs on page load (:210-212, :574-589). **Dashboard (`AdminController@admin_dashboard`):** - `sale_this_month`, `admin_sale_this_month`, `seller_sale_this_month` and `total_inhouse_sale` are BASE shown with `single_price`, i.e. today's rate (`dashboard.blade.php:237, 255, 266, 588`). - `total_sale` is BASE shown with `number_format_short` and no currency (:226). - `cached_graph_data` (`Cache::remember` 86,400 s, :49-72) sums the lifetime `num_of_sale` across every product and is never forgotten. **UNVERIFIED** whether the view still uses it. - `$admin_id` is the first admin only. **SQL injection (staff-only):** `$request->interval_type` is concatenated into raw SQL in the dashboard AJAX blocks (`AdminController.php:282, 316, 337, 373, 387, 429`), e.g. `'... INTERVAL 1 ' . $request->interval_type . ')'` at :282. These routes are inside the admin group, so any staff account can inject. **Currency:** the AOV chart shows BASE while its KPIs show SYP (RC:2262). Wallet and commission history use today's rate or no currency. **Offset `created_at`:** used by nearly every RC report. Only PMS, `OrdersExport` and parts of `top_customer_details` use the `orders.date` epoch. ### 3.4 Exports (`app/Exports`) | Class | Called from | Currency | Loading | |---|---|---|---| | `TopCustomersExport` | RC:56 | BASE raw; includes sellers and admins | `FromCollection` (all rows) | | `SalesProfitReportExport` | RC:2206 | BASE; filters are a copy of the page's | all rows | | `CartReportExport` | `CartLogController:82` | — | all rows | | `CompletedOrdersExport` | `OrderController:230` | `single_order_price` ✔ | all rows | | `CouponUsageReportExport` + `CouponUsageReport/*Sheet` | `CouponReportController:180` | SYP ✔ | `FromQuery` (chunked) | | `OrdersExport` | `OrderController@orderBulkExport` (Admin → Orders → Export, for the accountant) | **US dollars** (BASE, no rate) | `FromQuery` over the list's own query (`OrderController::filteredOrdersQuery()`, scope from the form's hidden `list` field), so it follows every filter of the page. **One row per order**; per product line, joined with ` | ` in the Products order: quantity and per piece the seller price and the price before discount (`OrderLinePricesUtility`: kept on `order_details.seller_price` / `price_before_discount` since 2026-10-10, else the product's current prices), the purchase price (frozen, else current) and the price after discount (`price / quantity`); `-` = none. Then total pieces, total purchase (live lines), products total, coupon, coupon discount, shipping, order total (products total − coupon + shipping). Pinned by `OrdersAccountantExportTest` | | `CustomersExport` | `CustomerController:142` | `balance` raw | `FromQuery` | | `ProductExport` | `ProductController:96` | UNVERIFIED | `get()` | `Seller/OrderController@orderBulkExport` uses a different class, `App\Models\OrdersExport` (ticked ids only). ### 3.4a Seller price currency and the reports (2026-10-25) Order money is unchanged: `order_details.price/tax/purchase_price`, `orders.*`, `commission_histories.seller_earning/admin_commission` stay BASE (USD) for every seller, so every report on them is unchanged. RAW columns (`products.*` prices, `product_stocks.*`) are in `products.price_currency`: sum or compare them only through `raw_to_base_sql()` (its `CASE` follows the currency) or `product_price_to_syp()`. Fixed in that change: the earning "profit report" (bare RAW numbers, now labelled per product currency, dollars as before), the Sales & Profit per-order rows and detail modal (they took the stock price as the cost; now the frozen purchase price, else the stock price at the product's rate, like the totals), Seller Profitability "due" (the balance is in the seller's currency). The admin product export gained a `Price Currency` column. Reporting views: section F of the SQL file. ### 3.5 Reporting database user (`kapitano_reports`) **What it is.** Management's own assistant builds its daily, weekly and monthly reports straight from the database. It connects as a read-only MySQL login, `kapitano_reports@127.0.0.1`, reached through an SSH tunnel. - That login sees **only** the schema `kapitano_reports`. The schema holds views over the live tables, and each view keeps the real table's name: `kapitano_reports.orders`, `.order_details`, `.combined_orders`, `.products`, `.shops`, `.users`, and so on. - The views are owned by a locked account, `kapitano_reports_definer@localhost`, which has SELECT on the listed tables and nothing else. - Everything is created by `sqlupdates/kapitano_reports_readonly_access_2026-10-04.sql`. Its header is the runbook: preconditions, SSH key restrictions, creating the login by hand, and rollback. It is not a migration: the app's DB user must not hold CREATE USER. **Why views and not column grants.** No personal data may be exposed. - An order's governorate and city live only inside `shipping_address` JSON, next to the customer's name and phone. The views extract `ship_state` / `ship_city`. - `payment_details`, `additional_info`, `cancel_note`, ticket text, cart snapshots, audiences, bank columns, tokens and IPs are never selected. - A raw guest `temp_user_id` is also never selected, because it works as the guest's credential in the app. Carts get a hashed `guest_key` instead. **What the views compute for the reader:** | Column | Meaning | PHP original | |---|---|---| | `rates.syp_rate_now` / `market_multiplier` | Today's BASE → SYP factor, and the RAW → BASE factor for market products | `currency_syp_factor()`, `raw_to_base_multiplier()` | | `orders.syp_rate`, `order_details.syp_rate`, `combined_orders.syp_rate`, `*_syp` | Rate on the order's own date (a checkout uses its oldest order) | `exchange_rate_at(order_timestamp())` | | `order_day`, `order_at_syria` | Syria date and time from the epoch `orders.date` (UTC+3), whatever the client's session timezone | `PlatformMetricsService` buckets | | `combined_orders.shipping_fee` / `payable_total` | Delivery fee of the checkout / what the customer owes | `combined_order_payable_total()` | | `products.base_unit_price` / `base_purchase_price` | RAW prices converted to BASE | `raw_to_base_sql()` | **These are SQL copies of PHP rules.** Changing the exchange-rate rule, the payable-total rule, or renaming or dropping any column a view reads breaks management's reports. When that happens: 1. update the SQL file and bump the `about` view's version; 2. re-run the file on the server; 3. tell management **before** deploying. `tests/Feature/ReportingViewsRuntimeTest.php` is opt-in and live. It is skipped while the schema is absent. It compares every order's and checkout's rate and payable total with the PHP functions, checks a day's sales against `PlatformMetricsService`, and fails if a personal column appears. **Notes for whoever writes the reports:** - **Void orders** are `delivery_status IN ('cancelled','returned')`, available as `is_void`. - A return is `returned`, plus a reason key in `cancel_reason`. - A partial return shows only on `order_details.delivery_status`. - There is no refunds table. - **Delivery fee** is per checkout: `combined_orders.shipping_fee`. `orders.grand_total` does not include it. Count it once per `combined_order_id`, never per seller order. Most areas charge 0 (`cities.cost`). - **Discounts** are coupons only: - `orders.coupon_discount` is each seller order's share; `combined_orders.coupon_discount` is the checkout total. - Product (fake) discounts are already inside `order_details.price`. - **Cost price** is `products.purchase_price`, RAW, and is **today's** value: orders do not keep a copy. For partner products it is derived from `partner_sources.cost_discount_percent`. - **Commission:** - The rule is in the `settings` view: `seller_commission_type`, then `vendor_commission`, or `shops.commission_percentage`, or `categories.commision_rate`. - `commission_histories` holds the amounts actually booked, and misses most cash-on-delivery orders. `PlatformMetricsService::commissionFor()` estimates commission from the rule instead. - Since seller pricing (2026-10-06) an order line keeps its cost: `order_details.purchase_price` (BASE, whole line), and its commission is `price − purchase_price`. The reporting views do not expose that column yet: adding it means updating `sqlupdates/kapitano_reports_readonly_access_2026-10-04.sql` and telling management. - **Dates.** Use `order_day`. Laravel `created_at`-style columns are another matter: - This covers `users.created_at`, `delivered_date`, `payment_settled_at`, `cancelled_at` and the follow-up times. - They are Syria time from 2026-09-02 on. Before that they are 8–9 h ahead: 9 h until March 2026, then 8 h. - **Not stored at all:** - result counts of a search: "searched and found nothing" can only be approximated as a `search_submitted` event with no click; - the cash amount collected per driver: only who delivered (`order_follow_ups.delivery_employee_id` → `delivery_agents`) and when the order was marked paid (`payment_settled_at`); - web Paymera attempts: only the last `combined_orders.payment_id` survives, while the app logs every attempt in `paymera_app_checkouts`. - **Section C of the file** covers `order_ratings`, `traffic_*` and `users.deactivated_at`. It only works once those features are deployed. Until then the run stops there and harms nothing. --- ## 4. Marketing features ### 4.1 WhatsApp campaigns (BuzzBip) **Tables** Created by `database/migrations/2026_09_24_000001_create_whatsapp_marketing_tables.php`. Templates come from `2026_09_24_000005`. | Table | Key columns and model notes | |---|---| | `marketing_contacts` | phone (unique, E.164), name, tag, notes, `opted_out`, created_by. Model `MarketingContact`. | | `whatsapp_campaigns` | name, template_id, coupon_id / coupon_code, discount_text, name_fallback; `audience` JSON (resolved only when sending starts); `status` `scheduled \| preparing \| sending \| paused \| completed \| cancelled`; scheduled / started / finished / polled timestamps; denormalised recipients / accepted / failed / delivered / read / clicked counters; later `template_ref_id`, `template_values`, `header_media_id`, `header_image`. Model `WhatsappCampaign`: `isEditable()` = never started and scheduled or paused (:75); `isDeletable()` = not preparing or sending (:82). | | `whatsapp_campaign_recipients` | campaign_id, user_id, contact_id, name, phone; `send_status` pending/accepted/failed, attempts, `bulk_group_id`, `wa_state` accepted/sent/delivered/read/failed, delivered_at, read_at; click fields **`token`** (16 chars, unique), clicked_at, clicks, clicked_store. Unique `(campaign_id, phone)`. | | `whatsapp_templates` | buzzbip_id, name, category, language, header_type none/text/image, body; `variables` JSON (first_name, coupon_code, coupon_discount, custom); `buttons` JSON (at most 10 buttons, 2 URL, 1 phone); `state` DRAFT/PENDING/APPROVED/REJECTED or local ARCHIVED. `CLICK_BASE` is hard-coded to `https://kapitano.shop/wc/` (`WhatsappTemplate.php:54`), so tracking links always point at production. | **Create flow** `WhatsAppCampaignController@store` (:212) → `campaignInput` (:359-424): - validates the input; - resolves the audience once, just to count it, and refuses 0 recipients; - optionally uploads a header image via `BuzzBipService::uploadMedia`; - creates the row with status `scheduled`. Other controller actions: | Action | Lines | Notes | |---|---|---| | Edit | :239-293 | Locks the row and rebuilds recipients | | Delete | :314-348 | Also archives an unused template | | Test send | :458-501 | One real message, token `test` | | Export | :580-614 | Streamed CSV, chunks of 1000. Names are not escaped for leading `=+-@` (CSV formula injection) | **Engine: `WCU::process($batch)` (:1151-1199)** Run every minute by `whatsapp-campaigns:process`. Constants (:35-52): `BATCH_SIZE` 100, `MAX_ATTEMPTS` 3, `POLL_DAYS` 3, `POLL_EVERY_MINUTES` 5, `ATTRIBUTION_DAYS` 7, dial code 963. 1. **Due campaigns** go to `prepare()` (:922-971). - A guarded UPDATE moves `scheduled → preparing` (atomic claim). - `resolveAudience()` (:440-605) runs, and recipients are `insertOrIgnore`d in chunks of 500 with token `Str::lower(Str::random(12))`. - A guarded UPDATE then moves `preparing → sending`. - On an exception the campaign goes to `paused` with a note. - A process killed mid-prepare leaves the campaign in `preparing` forever. It can only be cancelled. 2. **Sending campaigns** go to `sendBatch()` (:978-1069). - Takes the next 100 pending rows and sends each with `BuzzBipService::sendTemplate`. - Re-reads the campaign status every 20 messages, so pause and cancel take effect quickly. - HTTP 403 pauses the whole campaign ("balance probably empty"). - 429, 5xx or a connection error: `attempts++`, the row stays pending, and the batch stops. - Any other error marks the row failed. - When nothing is pending, the campaign becomes `completed`. 3. **Poll** (:1077-1123). - Covers campaigns started within the last 3 days, polled more than 5 minutes ago. - `BuzzBipService::messageStates()` (:359) sets `wa_state`, `delivered_at` and `read_at`. - BuzzBip has no webhook, so polling is the only source of delivery state. **Audience: `resolveAudience()`** Sources, deduplicated by E.164 phone: - `CustomerSegmentUtility` customers; - picked users; - contacts that are not opted out (optionally by tag); - carts not ordered (`CustomerSegmentUtility::cartQuery`); - pasted numbers. Removed afterwards: - opted-out numbers; - numbers messaged within `skip_recent_days`; - the **Exclude** block (`excludedPhones` :612): phones, users, contact tags, recipients of earlier campaigns by scope, cities; - customers who ordered within `skip_ordered_days`; - customers who already used the coupon (:577-603). Opt-out is a manual admin toggle only (controller :832). There is no inbound STOP. Opt-out is **not re-checked at send time**, so a number opted out after prepare still gets the message. **Per-message values: `sendParams()` (:177-226)** - `first_name` comes from `firstName()` (:330). It falls back to `name_fallback` or "صديقنا" when the name is shorter than 2 characters, has no letters, or has digits. - `cleanParam()` (:346) strips the newlines and tabs that Meta rejects. **Attribution: `results()` (:1213-1285)** Orders from `started_at` to +7 days, not cancelled, filtered by `coupon_code` if the campaign has one. **Click tracking** - Route: `GET /wc/{store}/{token}` (`routes/web.php:784-787`), `where store = g|a|\d`, `throttle:60,1`, **no auth**, declared before the `/{slug}` catch-all. - Handler: `WhatsAppCampaignClickController@open` (:21-41). - The token is lower-cased and stripped to `[a-z0-9]`. - `WCU::recordClick()` (:1290-1315) sets `clicked_at` once, increments `clicks`, and bumps the campaign's `clicked_count` on the first click only. - The request then redirects with `redirect()->away(WCU::clickTarget())` (:252-267). The target is the template button's admin-set URL, else the Play or App Store URL setting, else `url('/')`. - **No open redirect:** the destination never comes from the request. - Tokens are random (12 chars), not signed. Anyone holding a forwarded message can inflate clicks, up to 60/min per IP. - Targets have no domain allowlist beyond `^https?://` (`WhatsAppTemplateController.php:152-158`). **Templates: `WhatsAppTemplateController`** | Action | Line | BuzzBip call | |---|---|---| | store / resubmit | :337 | `createAndSubmitTemplate` (service :432) → state PENDING | | delete | :363 | remote delete; local row marked ARCHIVED | | recheck | :404 | `templateState` | | import | :208 | `listTemplates` | The index auto-rechecks non-final states older than 5 minutes (:37). ### 4.2 Customer segmentation (shared) - `app/Utility/CustomerSegmentUtility.php` plus `resources/views/backend/marketing/audience/_customer_filters`, `_filters_js`, `_range_links`. - Used by WhatsApp campaigns (`create.blade.php:188, 240, 426`) and custom push (`backend/notification/custom_notification.blade.php:117, 156, 184`). - Filters (`apply` :116-219): - segment: all / ordered / never / inactive N days, by `orders.date`, non-cancelled; - city, join date; - purchases (`applyPurchases` :260-340): checkout count via `COALESCE(combined_order_id, id)`, spend as `SUM(grand_total × order_rate_sql)`, categories with their subtree, brands; - app user (has a Sanctum token), wishlist, birth month, age, gender. - `cartQuery` (:376-423) reads `cart_retargets` and **calls `CartRetargetUtility::sync()`** (:384). Merely opening a campaign or notification form rewrites retargeting rows. - No SQL injection was found: raw fragments use bindings, ids pass through `ids()`, and enums are allow-listed. ### 4.2a Profile completeness, popup audiences, Customer Profiles report (2026-11-09) - **One rule:** `App\Services\Customers\ProfileCompleteness` (docblock = the rule). Complete = name + gender (male / female) + birthday (not in the current year: the app's untouched picker; 1 January accepted) + a verified phone (`users.phone_verified_at`, `PhoneVerification`) + one address with a governorate and a city; e-mail optional. PHP (`missing()`, `summary()`) and SQL (`applyComplete()`, `applyBirthday()`, `applyAddress()`...) agree, pinned by `ProfileCompletenessTest`. - **`users.profile_completed_at`** (migration `2026_11_09_000001`, index) = the **first** time the profile was complete, never cleared or moved. Stamped by the `User::saved` / `Address::saved` hooks (`refresh()`), and hourly by `customers:profile-completion` (`stampMissing()`) for query-builder writes. The migration dated the profiles already complete (`backfill()`): the latest of registration, phone confirmation, first address and the first audit giving name / gender / birthday a value. User audits start 2026-09-19, so older dates are approximate. On the local copy: 2,572 of 8,454 customers, in 2.8 s. - **Marketing filters:** `CustomerSegmentUtility` has `birthday` / `address` (`any|known|unknown`) and `profile` (`any|complete|incomplete`), all read through `ProfileCompleteness`. They are in the shared `_customer_filters` partial, so WhatsApp campaigns, custom notifications and the app popup all have them. - **App popup audience and button:** `App\Services\Customers\PopupAudience`, columns `app_popups.audience` / `action` (migration `2026_11_09_000002`, NULL = everyone / close only, as before). - Viewers: `everyone`, `guests`, or `customers` + the marketing filters. The customers option only shows to active customers (not banned or deactivated). - Checked in `app_popup_for_viewer()` for every popup, coupon or general. The audience travels in the payload as `_audience` / `_key` and is removed there, so it is never sent. - A customer's answer is cached 10 minutes per popup version. Reaching a complete profile forgets it. - The button (`action`) is a link or nothing (`{type: url, url}` or NULL); the profile page is simply the link `/profile`, which the app opens as a deep link. The web popup's button opens it. - The audience is set on Marketing → App popup (general popups). A coupon popup made from the coupon screen stays for everyone, limited by its coupon's own rules. - **Several popups at once** (migration `2026_11_09_000004`, `app_popups.priority`, at most `AppPopup::MAX_ACTIVE` = 5): each viewer gets the first in staff's order whose audience they are in. `app_popup_payload()` = the first live popup + `_candidates` (all, in order). `app_popup_for_viewer()` returns one and strips the internal keys, so the app's `popup` is unchanged. The website's cached payload translates each candidate's button. - Popups page: up / down (`app-popups.move`), per popup "in its audience / get it after the popups above" (`PopupAudience::reach()`, customers only, coupon rules not counted), and "which popup does this customer see?" by phone (`preview()`). - Read the switched-on rows with `activeAll()` / `activeForCoupon()`, never `active()` alone. The coupon screen did, and it is now per coupon; the coupon list badges every coupon with a popup on. - App contract: `docs/api/app-popup-audience.md`. - **Account bar:** the website profile page (`frontend/user/profile`, laid out like the app) and `profile/counters` → `profile_completion` (`summary()`). - The website form (`HomeController@userProfileUpdate`) no longer writes the phone, the account's own country / state / city, or a password. Before, it wrote the phone as typed, with no check that another account held it. - E-mail is a plain optional field, unique, and required for an account without a phone. Before, the "change e-mail" box said a mail was sent and changed nothing. - **Verified phone** (`users.phone_verified_at`, `App\Services\Customers\PhoneVerification`, migration `2026_11_09_000003`): - Customers who had a phone count as verified; those without one do not (580 on the local copy). - Afterwards only a WhatsApp code stamps it: `OtpGuard::markVerified()` (every code-accepting path) stamps the account holding the number, and the `User::saving` hook stamps a phone saved while OtpGuard holds its proof (registration, checkout account, adding a phone). A phone saved by admin / staff in the panel counts as verified too (`staffIsWriting()`: add / edit customer, Excel import). A phone changed without a code loses the stamp (`User::saved`, written to the row). - No customer changes a phone from the profile. One without a phone adds it with a code: on the website `ProfilePhoneController` (`profile.phone.send|verify|cancel`, a card on the profile page, `09…` read as Syrian); in the app `send-otp` + `verify-otp`, then `profile/update` (same number ignored, a different one or an unproven new one refused; contract `docs/api/app-popup-audience.md` §6). Pinned by `PhoneVerificationTest`. - **Customer Profiles** (ملفات العملاء, formerly Customer Demographics; `reports.demographics`, `CustomerDemographics`): account only, with no orders or money. - The period is by registration or by `profile_completed_at` (`basis`). - Shows profile parts, gender, age groups (under 18 is its own group; it used to fall into 55+), governorate, what is missing, per month, and the customers list + CSV. That list, with `basis = completed` and a campaign's dates, is where a draw's winner is picked. - A customer's governorate = their default address, else the newest one, through one grouped subquery. A per-row correlated subquery in the join ran for minutes on the local copy. ### 4.3 Cart retargeting - **There is no `CartLog` model and no `cart_logs` table.** "Cart logs" and the "Cart report" read the live `carts` table (`CartLogController`, 91 lines). - `CartRetargetController` (610 lines): - `index` (:32-88) runs `CartRetargetUtility::sync()`, throttled by a 60 s `Cache::add` lock, then shows filters, KPIs and `?export=csv`. - `update` (:90-170): - validates `contact_status` (pending / contacted / no_answer / wrong_number) and `cart_status` (active / abandoned / converted / cancelled / closed); - `order_value` is entered in SYP and stored as BASE; - copies the shared fields to every open cart with the same phone, inside a transaction. - `claim` (:176) / `release` (:191) write `cart_retarget_holds` (unique `customer_key`). Admins can take over (`canTakeOver` :229). - `customers()` also gives each registered customer's row (2026-10-26) their orders, as the repeat-customer badge of the orders list (`CustomerOrderHistoryUtility`, one grouped query per page: every checkout so far, or those before the converted order once the cart became one), and their join date (`users.created_at`, shown as "Joined 2 days ago"). Guests get neither. Both are in the follow-up dialog and the CSV. Filter "Customer History" (`customer_history` = `new` | `returning`, in `applyScope()` so the KPIs follow it) asks the same question in SQL (`previousOrderSql()`); on the MySQL copy it agreed with the badge on every row. - `cart_retargets` table: - migrations `2026_09_09_102603`, `2026_09_12_000001` (snapshot columns), `2026_09_24_000002` (holds); - `cart_key` is `u:` or `g:`. - Sync (`CartRetargetUtility.php:77+`): - carts go to abandoned after `ABANDON_AFTER_MINUTES` = 60 (:29); - conversion lookback is 3 days (:32). - `GuestCartInfo` (`guest_cart_infos`, schema only in `sqlupdates/feature_guest_cart_infos.sql`, no migration) stores a guest's phone per `temp_user_id`. - Web writer: `POST /guest-cart-info` (`routes/web.php:149-152`). Uses the session `temp_user_id`, has CSRF, no throttle. - API: `GET` and `POST api/v2/guest-cart-info` (`routes/api.php:275-278`, `Api/V2/GuestCartInfoController.php`). **Unauthenticated; anyone with a `temp_user_id` can read or overwrite the phone** (see §8). - `carts:send-reminders` is described in §6. ### 4.3a Order rating follow-up (2026-10-26) Customer service calls customers whose delivered purchase is not rated yet. Admin → Order ratings has two tabs (`backend.order_ratings._tabs`); the follow-up page is `backend/order_ratings/follow_ups/index`. Everything is in `app/Services/OrderRating/FollowUps/` (static classes; the map is `RatingFollowUps`' docblock). - **Tables** (migration `2026_10_27_000001`): - `order_rating_follow_ups`: one **case** per checkout (`combined_order_id` unique). Since 2026-11-11 (migration `2026_11_11_000001`) it also holds the "after rating" complaints, told apart by `kind` (`not_rated` / `low_rating`; `order_rating_id` and `rating` are the complaints' own columns), unique per (`kind`, `combined_order_id`): a checkout may have one case of each kind. See `FollowUpCases`' docblock: every query is limited to one kind (models through `App\Models\Concerns\RowsOfSharedTable`, query-builder queries through `FollowUpCases::notRated()` / `lowRating()`), because the "did not rate" sync closes cases a complaint must never lose. The complaints were copied with ids above every old id and their call history renumbered; `low_rating_follow_ups` is left unread until dropped. - `order_rating_follow_up_agents`: one row per **customer** (`customer_key` unique: `u:`, else `p:`). This is what stops two agents taking the same customer: of two simultaneous takes, the `insertOrIgnore` lets one in. - `order_rating_follow_up_logs`: every call, assignment and automatic change, never overwritten. Since 2026-11-10 (migration `2026_11_10_000004`) it is the call history of **both** tabs, told apart by `kind` (`not_rated` / `low_rating`, `FollowUpLogs`; models `OrderRatingFollowUpLog` / `LowRatingFollowUpLog` are scoped to their kind by `App\Models\Concerns\RowsOfSharedTable`). `follow_up_id` points at a different case table per kind: never query it without the kind. `low_rating_follow_up_logs` was copied in and left in place, unread, to be dropped by a later migration. - **Cases (`FollowUpSync`).** A case is a checkout that is delivered by the rule of `combined_delivery_status()`, written in SQL (`deliveredCheckouts()`), and has no `rated` `order_ratings` row. - Older checkouts with no rating row count too ("Not asked"). - The sync runs from the page (at most once a minute) and from `order-ratings:follow-ups` (hourly). - On the local copy it created 993 cases from 1,002 delivered checkouts. - Rating status: Not asked / Waiting / Dismissed ("not now") / Rated. - **Statuses.** - Open: `pending`, `contacted`, `no_answer`. - Closed: `declined`, `wrong_number`, `no_response` (set by the agent); `rated`, `cancelled` (set by the system only). - **Automatic changes.** - `rated`: through the listener `UpdateRatingFollowUp` on `OrderRatingSubmitted`, and through the sync. A closed case still turns `rated`. `rated_after_contact` is set when the rating came after the first call. - `cancelled`: the checkout left "delivered" (`OrderRatingWithdrawn`, or the sync). The case reopens when the checkout is delivered again. - `no_response`: 7 days after the last call while `contacted` / `no_answer`, unless "call again on" is later. - A supervisor can reopen anything closed except `rated` and `cancelled`. - **Assignment (`FollowUpAssignments`).** - An agent takes an unassigned customer, or saves a call, which takes them first. - A supervisor assigns, bulk-assigns, reassigns ("Reassign" ticked) or unassigns. - Next to each customer, on both tabs, a supervisor gets one "Assign" menu (`follow_ups/_assign_menu`, 2026-11-08) that does all of it: "Take this customer", then every agent (moving a customer someone holds: the button reads "Reassign", the current agent is ticked) and "Unassign"; an agent without the supervisor permission keeps the plain "Take this customer" button. One form per customer; each item posts `agent_id` to the assign route, or `customer_key` to take / unassign (`formaction`). The card clips its corners, so the page lets the open menu out (`.orf-customer.is-menu-open`). - Only the holder records calls; there is no take-over for admins. - The agent is copied onto the customer's open cases (`agent_id`, which the list and the per-agent numbers read), and a new case of that customer joins them. - The customer is freed once nothing of theirs is open. - A customer whose agent made no call within 48 hours of being assigned **stays with that agent** (since 2026-10-27; before, they went back to unassigned, log `released`). The page shows the agent's badge red with "did not respond" (`FollowUpAssignments::overdueQuery()`, live: a call by that agent since the assignment ends it). The supervisors get one bell notification per agent, **once per assignment** (`order_rating_follow_up_agents.overdue_notified_at`, migration `2026_10_27_000003`; log `overdue`; `OrderRatingFollowUpNotification`, `kind = overdue`, its link opens that agent's customers). A reassignment clears it and starts the 48 hours again. The badge (2026-10-28) is solid red with a warning icon and reads "agent did not call within 48 hours · N hours/days late" plus the assignment date; the filter "Agent follow-up" (`agent_late`: late / on_time, `FollowUpList::applyAgentLate()`) lists them. It is the agent's side only: the customer's side is the follow-up status, whose labels now say "Customer did not answer" / "Customer did not respond". - A supervisor assigning someone else sends that agent one notification per batch (`kind = assigned`). - Both kinds show in the bell dropdown itself, at the top of the Orders tab under "Follow-up assignments", with the order follow-up alerts: `NotificationUtility::STAFF_ASSIGNMENT_TYPES` feeds `unreadDropdownTabs()` and the 30-second refresh (`orders.staff_notifications`). Holders of the follow-up permissions get the bell even without `view_notifications` (`NotificationUtility::staffAssignmentPermissions()`). Before this, they appeared only under "View All Notifications". - **Calls (`FollowUpCalls::record`).** Each ticked case gets its own status; the note and "call again on" are shared. Agents never enter a rating: the row copies the link to the customer's order page. - **The page (`FollowUpList`).** - One row per customer: their cases matching the filters, plus every open case of theirs (marked "outside the filters"). - Each row shows the last contact (any case, any time), the full history, a "Contacted N days ago" badge (within 30 days), and a badge when another agent holds the same phone on the cart retargeting page (`cart_retarget_holds`). The badge blocks nothing. - Banned or deactivated customers, and customers with no phone, are never listed. - Each row also shows how many of the customer's purchases delivered in the page's date range they rated and their average stars (`ratingHistory()`: delivered checkouts by their last delivery, rated ones included; all time when the range is all). Information only: nothing is blocked. - Defaults: delivered in the last 30 days, delivered more than 48 hours ago, open cases, "mine or not assigned" (supervisors: everyone). - The numbers (open / unassigned / contacted / rated after contact / conversion, plus the per-agent table) follow the date, age, search and rating filters, but not the status and agent filters. - CSV: one row per case. - **Permissions.** - Opening the page and the CSV: **every employee** (the admin route group: admin / staff), read only - a notice says so, the take / record / assign / unassign / reopen controls are hidden and the routes refuse them (decided 2026-10-28). The side menu shows the entry to all; the two tabs show for `view_order_ratings` holders. Read-only viewers start on everyone's customers. - `follow_up_order_ratings` (agent): granted to CX, CX manager, CEO and full access. - `assign_order_rating_follow_ups` (supervisor): granted to CX manager, CEO and full access (migration `2026_10_27_000002`; the roles edit page lists only permissions the editing account holds, `@can`, so "full access" staff did not see them before). - Roles are matched by name, case-insensitively ("Cx Manager" on production). Agents and supervisors are whoever holds the permission through a role or directly; the Super Admin bypass does not count. - **Deploy:** `php artisan migrate` (tables, permissions, Arabic strings), then `php artisan cache:clear`. The page's first sync creates the cases. #### Customer notes and "Follow-up after rating" (2026-11-07) Decided with the user: customers get one agent across both tabs, plus notes of their own. Migration `2026_11_07_000002` (new tables only, no permission). - **Only the customer is assigned** (decided 2026-11-08, after a first version gave each order row a chip opening the order page's "متابعة الطلب الداخلية" modal): neither tab assigns orders one by one or edits the order's internal notes, and a call's note stays in the call history (`order_rating_follow_up_logs`, kind `not_rated` / `low_rating`). The order page's own follow-up (`order_staff_details`, `order_staff_notes`) is unchanged and is not shown here. - **Customer notes** (`CustomerNotes`, partial `follow_ups/_customer_notes`; stored in `order_staff_notes` with `notable_type = customer`, `notable_key` = the customer key, since 2026-11-10 - `App\Utility\StaffNotes`, migration `2026_11_10_000005`, which copied the old `order_rating_customer_notes` rows above every old id and moved their audits; the old table is left unread until dropped): notes about the person ("calls after 6 pm"), on both tabs. Agents write them; the writer, a supervisor or the Super Admin edit or soft-delete them. `revision` refuses a stale form, and every change is an `audits` row. Routes `admin.order_ratings.customer_notes.store|update|destroy` (agent permission). The form posts its card's `anchor`, so the page comes back to that card. - **"Follow-up after rating"** (third tab, `admin.order_ratings.after_rating.index|export|record|reopen`, `LowRatingFollowUpController`, view `after_rating/index`). The cases are purchases rated below `config('order_ratings.alert_below')`, the low-rating email's rule. It is laid out like "did not rate", one card per customer (`LowRatingList`), with the stars, comment and weak criteria under each order. - **Cases** (`order_rating_follow_ups` with `kind = low_rating` since 2026-11-11, before that `low_rating_follow_ups`; one per rating, `LowRatingCases::sync()`): created by the `OrderRatingSubmitted` listener and by `FollowUpSync::run()`. Both create the low case **before** the "did not rate" case closes as rated, so a customer's agent stays held across the rating. Ratings from before the tab are backfilled by the first sync. A rating whose order was deleted gets no case. - **One agent per customer on both tabs.** The tab shares `order_rating_follow_up_agents` with "did not rate": take / assign / reassign / unassign post to that tab's routes (`customerKey()` accepts a customer of either tab). `FollowUpAssignments::hasOpenCases()` / `applyToOpenCases()` and `FollowUpSync::pruneAssignments()` count both tables, and a call on either tab ends the 48-hour "did not call" flag (`overdueQuery()` reads the calls of both kinds in the one log table). Only the customer's agent records a call; recording on a free customer takes them. - **Statuses:** pending → contacted / no_answer (open) → resolved (closed). "Call again on" is shared by one call's orders. There is no automatic closing (a complaint is resolved, not timed out). A supervisor reopens, and the case rejoins whoever holds the customer. - **The page:** filters are status, agent (`agent_id`, the copy of the customer's agent), agent late and stars. Sort by newest, oldest, lowest rating or most orders. Numbers: low ratings, not contacted, contacted with the median time to the first call, resolved, plus the per-agent table for supervisors. CSV: one row per case. The tab's badge counts the last 30 days' low ratings nobody called. - **Cross-links:** a "did not rate" card shows the customer's open low ratings (`FollowUpList::rows()['low_open']`), and an "after rating" card shows their orders not rated yet. - **Who opens it:** `view_order_ratings` or `follow_up_order_ratings` (it shows customers' comments, unlike the "did not rate" tab, which is open to every employee). - **Tests:** `RatingFollowUpNotesTest` (SQLite; shares its schema with `OrderRatingFollowUpTest` through `Concerns/OrderRatingFollowUpTestSchema`) and a case in the opt-in `OrderRatingFollowUpRuntimeTest`. That class now snapshots the follow-up tables (and the order-note tables, to prove a call leaves them alone) and restores them in `tearDown`: on the local copy they are MyISAM and ignored the rollback, so every run used to leave its takes, calls and notes behind. - **Deploy:** `php artisan migrate` (tables, Arabic strings), then `php artisan cache:clear`. The first page load or hourly sync creates the cases of past low ratings. ### 4.3b Contact Us requests (2026-11-04) Contact Us is the one way customers and sellers write to us: **support tickets were removed** (decided with the user 2026-11-04; migration `2026_11_04_000003` backs up the `tickets` / `ticket_replies` rows and the two ticket permissions to `storage/app/backups/support_tickets_