fleetfuel/migrations/03_fuel_timezone_eat.sql

-- 03_fuel_timezone_eat.sql — fleetfuel · standardize fuel times to Africa/Nairobi
-- ─────────────────────────────────────────────────────────────────────────────
-- The source feed stamps record_datetime in UTC ("…Z"). The fleet operates in
-- Kenya (EAT, UTC+3), so storing UTC `timestamptz` made record_datetime::date
-- bucket by UTC day — an evening fill (e.g. 22:00 EAT = 19:00 UTC, fine, but
-- 01:00 EAT = 22:00 prev-day UTC) lands on the wrong calendar day in the daily
-- trend. Standardize every fuel timestamp to Africa/Nairobi WALL-CLOCK stored as
-- `timestamp` (without time zone): the value you read in the table is EAT, and
-- record_datetime::date is the EAT day.
--
-- Runs only after 01/02 (columns are still timestamptz here), so the USING
-- conversion `col AT TIME ZONE 'Africa/Nairobi'` (timestamptz -> local EAT
-- timestamp) is always correct. Idempotent in effect; safe to re-apply.
-- ─────────────────────────────────────────────────────────────────────────────

-- EAT-local cast: source UTC text -> Africa/Nairobi wall-clock timestamp (or NULL).
CREATE OR REPLACE FUNCTION fuel.to_ts_eat(p text)
 RETURNS timestamp LANGUAGE plpgsql STABLE AS $fn$
BEGIN
  RETURN (NULLIF(btrim(p), '')::timestamptz) AT TIME ZONE 'Africa/Nairobi';
EXCEPTION WHEN others THEN
  RETURN NULL;
END $fn$;

-- Views depend on record_datetime, so drop them before the column type change.
DROP VIEW IF EXISTS reporting.v_fuel_efficiency;
DROP VIEW IF EXISTS reporting.v_fuel_fills;

-- Convert existing data (timestamptz instant -> EAT wall-clock) and switch types.
ALTER TABLE fuel.records
  ALTER COLUMN record_datetime TYPE timestamp USING (record_datetime AT TIME ZONE 'Africa/Nairobi'),
  ALTER COLUMN deleted_at      TYPE timestamp USING (deleted_at      AT TIME ZONE 'Africa/Nairobi'),
  ALTER COLUMN ingested_at     TYPE timestamp USING (ingested_at     AT TIME ZONE 'Africa/Nairobi'),
  ALTER COLUMN updated_at      TYPE timestamp USING (updated_at      AT TIME ZONE 'Africa/Nairobi');
ALTER TABLE fuel.records
  ALTER COLUMN ingested_at SET DEFAULT (now() AT TIME ZONE 'Africa/Nairobi'),
  ALTER COLUMN updated_at  SET DEFAULT (now() AT TIME ZONE 'Africa/Nairobi');

ALTER TABLE fuel.ingest_state
  ALTER COLUMN updated_at TYPE timestamp USING (updated_at AT TIME ZONE 'Africa/Nairobi'),
  ALTER COLUMN updated_at SET DEFAULT (now() AT TIME ZONE 'Africa/Nairobi');

-- Trigger now derives EAT-local timestamps from raw.
CREATE OR REPLACE FUNCTION fuel.tg_records_derive()
 RETURNS trigger LANGUAGE plpgsql AS $fn$
BEGIN
  NEW.record_datetime     := fuel.to_ts_eat(NEW.raw->>'record_datetime');
  NEW.car_raw             := NEW.raw->>'car';
  NEW.plate               := fuel.norm_plate(NEW.raw->>'car');
  NEW.liters              := fuel.to_num(NEW.raw->>'liters');
  NEW.amount              := fuel.to_num(NEW.raw->>'amount');
  NEW.fuel_type           := fuel.canon_fuel_type(NEW.raw->>'fuel_type');
  NEW.fuel_type_raw       := NEW.raw->>'fuel_type';
  NEW.department          := fuel.canon_department(NEW.raw->>'department');
  NEW.driver              := NULLIF(btrim(NEW.raw->>'driver'), '');
  NEW.odometer            := fuel.to_num(NEW.raw->>'odometer');
  NEW.deleted_at          := fuel.to_ts_eat(NEW.raw->>'deleted_at');
  NEW.message_fingerprint := NEW.raw->>'message_fingerprint';
  NEW.updated_at          := now() AT TIME ZONE 'Africa/Nairobi';
  RETURN NEW;
END $fn$;

-- Recreate the read views (record_datetime is now EAT-local; fuel_date follows).
CREATE OR REPLACE VIEW reporting.v_fuel_fills AS
 SELECT r.id,
        r.record_datetime,
        r.record_datetime::date AS fuel_date,
        r.plate,
        d.vehicle_number,
        d.cost_centre,
        d.assigned_city,
        d.assigned_driver,
        d.imei,
        r.department,
        r.driver,
        r.liters,
        r.amount,
        r.fuel_type,
        r.odometer
   FROM fuel.records r
   LEFT JOIN LATERAL (
        SELECT dv.vehicle_number, dv.cost_centre, dv.assigned_city,
               dv.driver_name AS assigned_driver, dv.imei
          FROM tracksolid.devices dv
         WHERE r.plate IS NOT NULL
           AND fuel.norm_plate(dv.vehicle_number) = r.plate
         ORDER BY dv.enabled_flag DESC NULLS LAST, dv.updated_at DESC NULLS LAST
         LIMIT 1
   ) d ON true
  WHERE r.deleted_at IS NULL;

CREATE OR REPLACE VIEW reporting.v_fuel_efficiency AS
 WITH seq AS (
   SELECT id, plate, vehicle_number, cost_centre, assigned_city,
          record_datetime, fuel_date, odometer, liters, amount, fuel_type,
          odometer - lag(odometer) OVER (PARTITION BY plate ORDER BY record_datetime, id) AS km_since_last
     FROM reporting.v_fuel_fills
    WHERE plate IS NOT NULL AND odometer IS NOT NULL AND odometer > 0
 )
 SELECT seq.*,
        CASE WHEN liters > 0 AND km_since_last > 0 AND km_since_last < 5000
             THEN round(km_since_last / liters, 2)
        END AS km_per_litre
   FROM seq;

-- Dropping the views dropped their grants — re-grant to the read-only role.
DO $grants$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
    GRANT USAGE ON SCHEMA reporting TO grafana_ro;
    GRANT SELECT ON reporting.v_fuel_fills, reporting.v_fuel_efficiency TO grafana_ro;
  END IF;
END $grants$;