Here's the prompt: --- **Context** You are working on Snap & Bill, a utility submetering and billing SaaS platform built with PHP Laravel (backend, using Backpack for admin), Vue.js with Bootstrap-Vue (frontend), Flutter with Cubit (mobile), and MySQL. The platform manages water billing for condominium estates. Each estate (`site`) has a set of lots, each with one or more water meters. Meters are classified as either **private** (belonging to a residential or commercial lot) or **common area** (corridors, gardens, pool, etc.). Each site may or may not have a mains (public utility) meter. Billing runs in cycles (`open_sessions` for active, `archived_sessions` for closed). --- **Feature: Tanker Water (Bowser) Cost Allocation** When public water supply is insufficient, management orders a water tanker. Since tanker water enters the main storage tank unmetered and blends with mains supply, the cost must be allocated fairly across private lots. There's an existing algorithm in place that uses quote-part as fallback. This feature should be depreciated & replaced by the below. --- **Definitive Algorithm** **Part 1 — `attemptHydrate` (runs on every cycle close)** (app/Jobs/HydrateCommonConsumptionJob.php) Builds the historical common area consumption ratio. All five guards must pass in order before inserting: ``` IF site has no mains meter → skip IF tanker delivery recorded → skip (contaminated cycle) IF mains reading = 0 → skip IF any common area meter has missing or invalid reading → skip ELSE common_pct = Σ common area consumptions ÷ Σ all meter consumptions (private + common) × 100 INSERT INTO common_consumption (date, site_id, common_pct) VALUES (cycle_end_date, site_id, common_pct) ON DUPLICATE KEY UPDATE common_pct = new value ``` **Part 2 — Tanker allocation (runs when a tanker delivery is recorded for a cycle)** (app/Services/BowserAllocationService.php) ``` common_pct = AVG(common_pct) FROM common_consumption WHERE site_id = X IF common_consumption is empty for this site OR site has no mains meter OR mains reading = 0 → TV = 100% of tanker cost ELSE → TV = tanker cost × (1 − common_pct / 100) eligible_lots = private lots WHERE consumption > 0 this cycle total_private = Σ consumption of eligible_lots IF total_private = 0 → THROW "Tanker allocation is not possible" FOR each lot IN eligible_lots → lot_share = (lot_consumption / total_private) × TV ``` **Key rules:** - Tantième is never used anywhere in this feature - Zero-consumption lots are excluded from allocation entirely — they are not substituted or estimated - The `common_consumption` table is append-only from `attemptHydrate` and never written to by the allocation logic - A cycle with a tanker delivery must never hydrate `common_consumption` --- Your task is to plan for this change. assess the current gap in terms of api, migrations & impact on other modules. IDENTIFY any corner cases possible. Report with your findings & impementation plan **Responses** 1. Data Model Gaps 1a. common_consumption table — schema mismatch Current New spec Column common_ratio DECIMAL(8,6) — stored as 0.124500 common_pct DECIMAL(5,2) — stored as 12.45 Primary key / uniqueness no unique constraint (uses firstOrCreate on uuid) PRIMARY KEY (date, site_id) service_type_id present, part of lookup not in spec — spec queries WHERE site_id = X only archived_session_uuid present not in spec Required migration: rename common_ratio → common_pct, change scale (× 100 for existing rows), add UNIQUE(date, site_id), decide whether to drop service_type_id (see corner case §4). [@tdusmoha - Ignore the specs & dont touch the current schema. common_ratio & common_pct are the same data in different units. ] 1b. No common area meter classification Current model has is_main (mains meter) and is_billable (private lots). There is no way to identify dedicated common area submeters (pool, corridors). New spec formula: common_pct = Σ common area consumptions ÷ Σ all meter consumptions × 100 Current formula: common_ratio = (mains_reading − Σ all_submeters) / mains_reading These are fundamentally different if common area meters exist as separate submeters. The current approach infers common area usage as the difference between mains inflow and all submeters. The new spec sums common area meters directly. Required: Add is_common_area BOOLEAN DEFAULT false to lots table. Existing data: all non-main, non-billable lots should be set is_common_area = true on migration. [@tdusmoha Ignore the specs instructions about common area consumptions meter. We have private meters + an optional main meter. if that exists we can deduce common_consumption else we allocate 100% of tanker cost to private meters where usage > 0] 2. CommonConsumptionService — attemptHydrate Gaps Guard 1 — Mains meter existence: missing explicit null check If no is_main=true lot exists, $mainLot->id throws a null pointer. Needs: if (!$mainLot) return null. [@tdusmoha, add this guard before dispatching the job, similar to guatd 2] Guard 2 — Tanker delivery recorded: not inside the service Currently handled at dispatch level in the controller (if tankerCost <= 0, dispatch job). The spec requires this guard to live inside attemptHydrate itself. Once a tanker delivery table exists, this becomes a DB check. Until then it relies solely on the caller not dispatching.[@tdusmoha - That's ok, leave the guard outside the service] Guard 4 — Any common area meter has missing/invalid reading: not implemented No check exists that all common area meters (is_common_area=true) have valid readings for this cycle UUID. A missing common area reading silently inflates the inferred common ratio. Formula — must change when common area meters are explicitly modelled Current: $privateConsumption = sum of all archived_sessions WHERE lot_id != mainLot->id $commonRatio = (mainConsumption - privateConsumption) / mainConsumption New spec (once is_common_area exists): $commonConsumption = sum of archived_sessions WHERE lot is_common_area=true $allSubConsumption = sum of ALL archived_sessions (private + common area) $commonPct = ($allSubConsumption > 0) ? ($commonConsumption / $allSubConsumption) * 100 : 0 Duplicate prevention — needs updating firstOrCreate currently keys on (site_id, service_type_id, archived_session_uuid). With the new UNIQUE(date, site_id) primary key, the uniqueness check changes. [@tdusmoha since we arwnt considering common_meters, drop this completely] 3. BowserAllocationService Gaps Column name and scale Reads avg('common_ratio') and computes 1 - avgCommonRatio. Must become avg('common_pct') and 1 - (avgCommonPct / 100). Fallback removal — breaking change Current: when common_consumption is empty OR total_private = 0 → falls back to allocateByQuotePart(). New spec: Empty common_consumption → TV = 100% of tanker cost (still allocates by actual consumption, not quote_part) total_private = 0 → throw exception "Tanker allocation is not possible" The entire allocateByQuotePart() method is deprecated and removed. New guards inside the service New spec requires the service itself to check: Site has no mains meter → TV = 100% of tanker cost Mains reading = 0 → TV = 100% of tanker cost Currently BowserAllocationService does neither check. Zero-consumption lots — behaviour clarified Current: iterates all private lots, zero-consumption lots get shareRatio = 0, allocated $0. Correct by coincidence. [@tdusmoha this is better for audit, lets keep it this way] New spec: eligible_lots = private lots WHERE consumption > 0. The total_private denominator must only sum eligible lots. Current code sums all private lots including those with no session row. Net result is the same if missing rows are treated as 0 — but the code should be explicit. service_type_id in allocation lookup Current common_consumption query filters by service_type_id. New spec doesn't. Once schema changes, this must be removed. 4. supportsBowserAllocation Endpoint [@tdusmoha: We can depreciate this completely] Current: returns true if all billable lots have quote_part. Quote-part fallback no longer exists, so this logic is obsolete. New meaning: "can allocation run?" = are there any private lots with consumption > 0 this cycle? This check now needs to query open_sessions, not lots.quote_part. 5. Corner Cases # Scenario Behaviour required 1 total_private = 0 at allocation time (all meters unread, building unoccupied) Throw "Tanker allocation is not possible". Frontend must catch and surface this to the admin before confirming cycle close. [@tdusmoha agreed] 2 Site has no mains meter attemptHydrate skips (guard 1). Allocation runs with TV = 100% tanker cost. [@tdusmoha agreed] 3 No historical common_consumption (first ever tanker) TV = 100% tanker cost — correct per spec. [@tdusmoha agreed] 4 service_type_id removal from common_consumption If a site meters both water and gas, a single common_pct per site blurs service types. Recommend keeping service_type_id in the table but removing it from the spec's lookup — or make this a water-only feature explicitly. Needs a decision before migration. [@tdusmoha agreed] 5 common_pct > 100 or < 0 after formula change Possible with bad data. Must add validation guard before insert, log and skip. [@tdusmoha Not an issue anymore] 6 Two tanker deliveries in one billing cycle Current API accepts a single tanker_cost per completed call. If management splits deliveries across the month, only one cost can be submitted. Consider whether tanker_cost should be the total across the cycle, or whether the spec needs a delivery table to sum from. [@tdusmoha We assure the user enters the total of all deliveries] 7 Cycle closed with tanker, then attemptHydrate guard 2 fails Currently guard 2 is caller-side only. If controller logic changes in future, a contaminated cycle could hydrate common_consumption. Guard should live inside the service. [@tdusmoha ignore] 8 Schema migration for existing common_ratio rows Existing rows store 0.1245. After rename to common_pct, they must be multiplied by 100 (UPDATE common_consumption SET common_pct = common_ratio * 100). Do this in the same migration. [@tdusmoha not required anymore]