-- ============================================================================
-- FleetLedger Complete PostgreSQL Schema, Immutable Ledger & RLS Policies
-- Timezone: Africa/Johannesburg | Currency: ZAR (stored in integer cents)
-- Compliance: South African POPIA Privacy & Audit Standards
-- ============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 1. ENUMS
CREATE TYPE user_role AS ENUM ('owner', 'manager', 'driver');
CREATE TYPE vehicle_status AS ENUM ('available', 'rented', 'maintenance', 'inactive', 'sold');
CREATE TYPE agreement_frequency AS ENUM ('daily', 'weekly', 'monthly');
CREATE TYPE agreement_state AS ENUM ('draft', 'active', 'paused', 'ended');
CREATE TYPE overpay_policy AS ENUM ('unapplied_credit', 'apply_existing_future');
CREATE TYPE submission_status AS ENUM ('pending', 'verified', 'rejected');
CREATE TYPE payment_method AS ENUM ('eft', 'cash', 'deposit_application');
CREATE TYPE ledger_entry_type AS ENUM ('charge', 'payment', 'credit', 'adjustment', 'reversal', 'deposit_hold', 'deposit_apply');
CREATE TYPE expense_category AS ENUM ('repair', 'routine_service', 'oil_change', 'tyres', 'brakes', 'licensing', 'insurance', 'fuel', 'custom');
CREATE TYPE document_type AS ENUM ('sa_id', 'drivers_licence', 'rental_agreement', 'custom');
CREATE TYPE document_status AS ENUM ('pending', 'accepted', 'rejected', 'expired');

-- 2. ORGANISATIONS & PROFILES
CREATE TABLE organisations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'ZAR',
  timezone TEXT NOT NULL DEFAULT 'Africa/Johannesburg',
  delinquency_red_periods INTEGER NOT NULL DEFAULT 2 CHECK (delinquency_red_periods >= 2),
  overpayment_policy overpay_policy NOT NULL DEFAULT 'unapplied_credit',
  data_retention_months INTEGER NOT NULL DEFAULT 60,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  first_name TEXT NOT NULL,
  surname TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  avatar_url TEXT,
  popia_consent_at TIMESTAMPTZ,
  archived_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE organisation_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  role user_role NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  invited_by UUID REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (organisation_id, user_id)
);

CREATE INDEX idx_org_members_user ON organisation_members(user_id, is_active);
CREATE INDEX idx_org_members_org ON organisation_members(organisation_id, role);

-- 3. VEHICLES
CREATE TABLE vehicles (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  internal_code TEXT NOT NULL,
  vehicle_type TEXT NOT NULL, -- e.g. 'motorcycle', 'sedan', 'hatchback', 'bakkie', 'van'
  make TEXT NOT NULL,
  model TEXT NOT NULL,
  year INTEGER CHECK (year IS NULL OR (year >= 1980 AND year <= 2100)),
  registration_plate TEXT NOT NULL,
  photo_storage_path TEXT,
  acquisition_date DATE NOT NULL,
  purchase_price_cents BIGINT NOT NULL CHECK (purchase_price_cents >= 0),
  initial_odometer_km INTEGER CHECK (initial_odometer_km IS NULL OR initial_odometer_km >= 0),
  status vehicle_status NOT NULL DEFAULT 'available',
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (organisation_id, internal_code),
  UNIQUE (organisation_id, registration_plate)
);

CREATE INDEX idx_vehicles_org_status ON vehicles(organisation_id, status);

-- 4. DRIVER INVITATIONS & PROFILES
CREATE TABLE driver_invitations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  driver_user_id UUID REFERENCES profiles(id),
  first_name TEXT NOT NULL,
  surname TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  token_hash TEXT NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ,
  archived_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 5. RENTAL AGREEMENTS & RATE SCHEDULE
CREATE TABLE rental_agreements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  start_date DATE NOT NULL,
  end_date DATE,
  frequency agreement_frequency NOT NULL,
  rate_cents BIGINT NOT NULL CHECK (rate_cents > 0),
  billing_anchor SMALLINT NOT NULL, -- 1..7 for ISO weekday (Mon=1), or 1..31 for day of month
  grace_period_days SMALLINT NOT NULL DEFAULT 1 CHECK (grace_period_days >= 0 AND grace_period_days <= 14),
  deposit_held_cents BIGINT NOT NULL DEFAULT 0 CHECK (deposit_held_cents >= 0),
  signed_agreement_path TEXT,
  state agreement_state NOT NULL DEFAULT 'draft',
  paused_reason TEXT,
  ended_reason TEXT,
  created_by UUID NOT NULL REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (end_date IS NULL OR end_date >= start_date)
);

-- Prevent conflicting active agreements on the same vehicle
CREATE UNIQUE INDEX idx_unique_active_vehicle_agreement
  ON rental_agreements(vehicle_id)
  WHERE state IN ('active', 'paused');

CREATE INDEX idx_agreements_org_state ON rental_agreements(organisation_id, state);
CREATE INDEX idx_agreements_driver ON rental_agreements(driver_id, state);

CREATE TABLE agreement_rate_changes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE CASCADE,
  previous_rate_cents BIGINT NOT NULL,
  new_rate_cents BIGINT NOT NULL CHECK (new_rate_cents > 0),
  effective_from DATE NOT NULL,
  reason TEXT NOT NULL,
  authorised_by UUID NOT NULL REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 6. RENTAL CHARGES (IMMUTABLE OBLIGATIONS)
CREATE TABLE rental_charges (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE RESTRICT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  period_label TEXT NOT NULL, -- e.g. '2026-W42' or '2026-10'
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  due_date DATE NOT NULL,
  grace_until_date DATE NOT NULL,
  amount_due_cents BIGINT NOT NULL CHECK (amount_due_cents > 0),
  is_reversed BOOLEAN NOT NULL DEFAULT FALSE,
  reversal_reason TEXT,
  idempotency_key TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (agreement_id, period_start)
);

CREATE INDEX idx_rental_charges_fifo
  ON rental_charges(agreement_id, due_date ASC, period_start ASC)
  WHERE is_reversed = FALSE;
CREATE INDEX idx_rental_charges_org_due ON rental_charges(organisation_id, due_date);
CREATE INDEX idx_rental_charges_vehicle ON rental_charges(vehicle_id, period_start);

-- 7. PAYMENT SUBMISSIONS & VERIFIED PAYMENTS
CREATE TABLE payment_submissions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE RESTRICT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  amount_cents BIGINT NOT NULL CHECK (amount_cents > 0),
  payment_date DATE NOT NULL,
  method payment_method NOT NULL,
  reference TEXT,
  proof_storage_path TEXT,
  status submission_status NOT NULL DEFAULT 'pending',
  rejection_reason TEXT,
  dispute_note TEXT,
  submitted_by UUID NOT NULL REFERENCES profiles(id),
  reviewed_by UUID REFERENCES profiles(id),
  reviewed_at TIMESTAMPTZ,
  idempotency_key TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_submissions_org_status ON payment_submissions(organisation_id, status, created_at DESC);
CREATE INDEX idx_submissions_driver ON payment_submissions(driver_id, created_at DESC);

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  submission_id UUID UNIQUE REFERENCES payment_submissions(id) ON DELETE RESTRICT,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE RESTRICT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  amount_cents BIGINT NOT NULL CHECK (amount_cents > 0),
  received_date DATE NOT NULL,
  method payment_method NOT NULL,
  reference TEXT,
  evidence_storage_path TEXT,
  verified_by UUID NOT NULL REFERENCES profiles(id),
  verified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  is_reversed BOOLEAN NOT NULL DEFAULT FALSE,
  reversed_at TIMESTAMPTZ,
  reversed_by UUID REFERENCES profiles(id),
  reversal_reason TEXT,
  idempotency_key TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_org_date ON payments(organisation_id, received_date DESC);
CREATE INDEX idx_payments_vehicle ON payments(vehicle_id, received_date DESC);
CREATE INDEX idx_payments_driver ON payments(driver_id, received_date DESC);

-- 8. FIFO PAYMENT ALLOCATIONS & CREDITS
CREATE TABLE payment_allocations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  payment_id UUID NOT NULL REFERENCES payments(id) ON DELETE RESTRICT,
  charge_id UUID NOT NULL REFERENCES rental_charges(id) ON DELETE RESTRICT,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE RESTRICT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  allocated_cents BIGINT NOT NULL CHECK (allocated_cents > 0),
  is_reversed BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_allocations_charge ON payment_allocations(charge_id) WHERE is_reversed = FALSE;
CREATE INDEX idx_allocations_payment ON payment_allocations(payment_id) WHERE is_reversed = FALSE;

CREATE TABLE credits_adjustments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  agreement_id UUID NOT NULL REFERENCES rental_agreements(id) ON DELETE RESTRICT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE RESTRICT,
  source_payment_id UUID REFERENCES payments(id),
  entry_type ledger_entry_type NOT NULL CHECK (entry_type IN ('credit', 'adjustment', 'reversal', 'deposit_apply')),
  amount_cents BIGINT NOT NULL, -- positive reduces driver debt / adds credit
  remaining_unapplied_cents BIGINT NOT NULL DEFAULT 0 CHECK (remaining_unapplied_cents >= 0),
  reason TEXT NOT NULL,
  authorised_by UUID NOT NULL REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 9. EXPENSES & DRIVER REIMBURSEMENTS (SEPARATE FROM RENTAL LEDGER)
CREATE TABLE expense_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
  agreement_id UUID REFERENCES rental_agreements(id) ON DELETE SET NULL,
  expense_date DATE NOT NULL,
  cost_cents BIGINT NOT NULL CHECK (cost_cents > 0),
  category expense_category NOT NULL,
  custom_category_label TEXT,
  service_provider TEXT,
  description TEXT,
  receipt_storage_path TEXT,
  paid_by TEXT NOT NULL CHECK (paid_by IN ('owner', 'driver_reimbursable')),
  driver_reimbursement_owed_cents BIGINT NOT NULL DEFAULT 0 CHECK (driver_reimbursement_owed_cents >= 0),
  created_by UUID NOT NULL REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_expenses_vehicle_date ON expense_entries(vehicle_id, expense_date DESC);
CREATE INDEX idx_expenses_org_date ON expense_entries(organisation_id, expense_date DESC);

-- 10. DOCUMENTS, NOTIFICATIONS & IMMUTABLE AUDIT LOG
CREATE TABLE document_records (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  driver_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  agreement_id UUID REFERENCES rental_agreements(id) ON DELETE SET NULL,
  doc_type document_type NOT NULL,
  custom_title TEXT,
  storage_path TEXT NOT NULL,
  file_size_bytes INTEGER NOT NULL CHECK (file_size_bytes > 0 AND file_size_bytes <= 10485760),
  mime_type TEXT NOT NULL CHECK (mime_type IN ('application/pdf', 'image/jpeg', 'image/png', 'image/webp')),
  expiry_date DATE,
  status document_status NOT NULL DEFAULT 'pending',
  review_note TEXT,
  reviewed_by UUID REFERENCES profiles(id),
  reviewed_at TIMESTAMPTZ,
  uploaded_by UUID NOT NULL REFERENCES profiles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  recipient_user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  category TEXT NOT NULL,
  link_path TEXT,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  idempotency_key TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organisation_id UUID NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  actor_user_id UUID NOT NULL REFERENCES profiles(id),
  actor_role user_role NOT NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id UUID NOT NULL,
  before_state JSONB,
  after_state JSONB,
  ip_address INET,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================================
-- ROW LEVEL SECURITY (RLS) — TENANT & DRIVER ISOLATION
-- ============================================================================

ALTER TABLE organisations ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE organisation_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE vehicles ENABLE ROW LEVEL SECURITY;
ALTER TABLE rental_agreements ENABLE ROW LEVEL SECURITY;
ALTER TABLE rental_charges ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_allocations ENABLE ROW LEVEL SECURITY;
ALTER TABLE credits_adjustments ENABLE ROW LEVEL SECURITY;
ALTER TABLE expense_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;

-- Helper: Check if authenticated user is active Owner/Manager of the organisation
CREATE OR REPLACE FUNCTION is_org_admin(org_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM organisation_members
    WHERE organisation_id = org_id
      AND user_id = auth.uid()
      AND role IN ('owner', 'manager')
      AND is_active = TRUE
  );
$$;

-- Policies: Owners see only their organisation; Drivers see only their own records
CREATE POLICY org_admin_select_vehicles ON vehicles
  FOR SELECT USING (is_org_admin(organisation_id) OR EXISTS (
    SELECT 1 FROM rental_agreements ra
    WHERE ra.vehicle_id = vehicles.id AND ra.driver_id = auth.uid()
  ));

CREATE POLICY org_admin_manage_vehicles ON vehicles
  FOR ALL USING (is_org_admin(organisation_id))
  WITH CHECK (is_org_admin(organisation_id));

CREATE POLICY agreements_select_policy ON rental_agreements
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

CREATE POLICY charges_select_policy ON rental_charges
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

CREATE POLICY submissions_select_policy ON payment_submissions
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

-- Drivers can only insert PENDING submissions for their own active agreement
CREATE POLICY driver_insert_submission ON payment_submissions
  FOR INSERT WITH CHECK (
    driver_id = auth.uid()
    AND submitted_by = auth.uid()
    AND status = 'pending'
    AND reviewed_by IS NULL
    AND EXISTS (
      SELECT 1 FROM rental_agreements ra
      WHERE ra.id = agreement_id
        AND ra.driver_id = auth.uid()
        AND ra.organisation_id = payment_submissions.organisation_id
    )
  );

-- Only Owner/Manager can verify payments or read expenses/audit logs
CREATE POLICY payments_select_policy ON payments
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

CREATE POLICY allocations_select_policy ON payment_allocations
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

CREATE POLICY expenses_owner_only ON expense_entries
  FOR ALL USING (is_org_admin(organisation_id))
  WITH CHECK (is_org_admin(organisation_id));

CREATE POLICY documents_access_policy ON document_records
  FOR SELECT USING (is_org_admin(organisation_id) OR driver_id = auth.uid());

CREATE POLICY notifications_recipient_only ON notifications
  FOR SELECT USING (recipient_user_id = auth.uid());

CREATE POLICY audit_owner_only ON audit_events
  FOR SELECT USING (is_org_admin(organisation_id));

-- ============================================================================
-- TRANSACTIONAL FIFO VERIFICATION & ALLOCATION RPC
-- Prevents double approval, enforces role check, allocates oldest charges first
-- ============================================================================
CREATE OR REPLACE FUNCTION verify_and_allocate_submission(
  p_submission_id UUID,
  p_actor_id UUID,
  p_idempotency_key TEXT
) RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_sub payment_submissions%ROWTYPE;
  v_org organisations%ROWTYPE;
  v_payment_id UUID;
  v_remaining BIGINT;
  v_charge RECORD;
  v_charge_unpaid BIGINT;
  v_alloc BIGINT;
BEGIN
  -- Lock submission row to prevent concurrent duplicate approvals
  SELECT * INTO v_sub
  FROM payment_submissions
  WHERE id = p_submission_id
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Submission not found';
  END IF;

  IF NOT is_org_admin(v_sub.organisation_id) OR v_sub.driver_id = p_actor_id THEN
    RAISE EXCEPTION 'Unauthorised: Only an organisation owner can verify payments';
  END IF;

  IF v_sub.status = 'verified' THEN
    SELECT id INTO v_payment_id FROM payments WHERE submission_id = p_submission_id;
    RETURN v_payment_id; -- Idempotent safe return
  END IF;

  IF v_sub.status != 'pending' THEN
    RAISE EXCEPTION 'Cannot verify a rejected submission';
  END IF;

  SELECT * INTO v_org FROM organisations WHERE id = v_sub.organisation_id;

  -- Mark submission verified
  UPDATE payment_submissions
  SET status = 'verified',
      reviewed_by = p_actor_id,
      reviewed_at = NOW()
  WHERE id = p_submission_id;

  -- Insert verified payment
  INSERT INTO payments (
    organisation_id, submission_id, agreement_id, vehicle_id, driver_id,
    amount_cents, received_date, method, reference, evidence_storage_path,
    verified_by, idempotency_key
  ) VALUES (
    v_sub.organisation_id, v_sub.id, v_sub.agreement_id, v_sub.vehicle_id, v_sub.driver_id,
    v_sub.amount_cents, v_sub.payment_date, v_sub.method, v_sub.reference, v_sub.proof_storage_path,
    p_actor_id, p_idempotency_key
  ) RETURNING id INTO v_payment_id;

  v_remaining := v_sub.amount_cents;

  -- Allocate FIFO across unpaid charges (oldest due_date first)
  FOR v_charge IN
    SELECT rc.id, rc.amount_due_cents, rc.due_date,
           COALESCE((
             SELECT SUM(pa.allocated_cents)
             FROM payment_allocations pa
             WHERE pa.charge_id = rc.id AND pa.is_reversed = FALSE
           ), 0) AS already_paid_cents
    FROM rental_charges rc
    WHERE rc.agreement_id = v_sub.agreement_id
      AND rc.is_reversed = FALSE
      AND (
        v_org.overpayment_policy = 'apply_existing_future'
        OR rc.due_date <= CURRENT_DATE
      )
    ORDER BY rc.due_date ASC, rc.period_start ASC
    FOR UPDATE
  LOOP
    EXIT WHEN v_remaining <= 0;
    v_charge_unpaid := v_charge.amount_due_cents - v_charge.already_paid_cents;
    IF v_charge_unpaid > 0 THEN
      v_alloc := LEAST(v_remaining, v_charge_unpaid);
      INSERT INTO payment_allocations (
        organisation_id, payment_id, charge_id, agreement_id, vehicle_id, driver_id, allocated_cents
      ) VALUES (
        v_sub.organisation_id, v_payment_id, v_charge.id, v_sub.agreement_id, v_sub.vehicle_id, v_sub.driver_id, v_alloc
      );
      v_remaining := v_remaining - v_alloc;
    END IF;
  END LOOP;

  -- Record unapplied overpayment credit if remaining > 0
  IF v_remaining > 0 THEN
    INSERT INTO credits_adjustments (
      organisation_id, agreement_id, vehicle_id, driver_id, source_payment_id,
      entry_type, amount_cents, remaining_unapplied_cents, reason, authorised_by
    ) VALUES (
      v_sub.organisation_id, v_sub.agreement_id, v_sub.vehicle_id, v_sub.driver_id, v_payment_id,
      'credit', v_remaining, v_remaining, 'Unapplied overpayment credit held on ledger', p_actor_id
    );
  END IF;

  -- Record immutable audit event
  INSERT INTO audit_events (
    organisation_id, actor_user_id, actor_role, action, entity_type, entity_id, after_state
  ) VALUES (
    v_sub.organisation_id, p_actor_id, 'owner', 'payment.verified', 'payment', v_payment_id,
    jsonb_build_object('submission_id', p_submission_id, 'amount_cents', v_sub.amount_cents, 'unapplied_credit_cents', v_remaining)
  );

  RETURN v_payment_id;
END;
$$;